Parameters not working.... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Parameters not working....

Author

Topic

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2012-02-09 : 10:34:05

I built this store procedure, but for some reason my paramters are not prompting me to enter any data in. Can someone tell me what I am doing wrong? I am a little confused. Thanks.

AS
Begin
declare
@primbottomdate datetime,
@primtopdate datetime,
@PrimProduct Varchar (50),
@PrimNetwork Varchar (50),
@Secbottomdate datetime,
@Sectopdate datetime,
@SecProduct Varchar (50),
@SecNetwork Varchar (50)

if exists (
select *
from dbo.sysobjects
where id = object_id(N'[dbo].[T_Primary_Secondary_Info]')
and OBJECTPROPERTY(id, N'IsTable') = 1)
drop table [dbo].[T_Primary_Secondary_Info]

Create Table T_Primary_Secondary_Info(
[cli_name] [varchar](35) NULL,
[ClientNumberOrg] [varchar](20) NULL,
[Primary Client] [varchar](35) NULL,
[Primary Acct Mgr.] [varchar](35) NULL,
[Primary Eff] [datetime] NULL,
[Primary Term] [datetime] NULL,
[Primary Address] [varchar](35) NULL,
[Primary City] [varchar](35) NULL,
[Primary State] [varchar](35) NULL,
[Primary Zip] [varchar](35) NULL,
[Primary County] [varchar](35) NULL,
[Primary Phone] [varchar](24) NULL,
[Primary Fax] [varchar](24) NULL,
[Primary Email] [varchar](60) NULL,
[PrimaryProduct] [varchar](5) NULL,
[PrimaryNetwork] [varchar](5) NULL,
[ac_PrimaryPCTSavingsSecondary] [decimal](10, 2) NULL,
[ac_PrimaryPCTNetGross] [decimal](10, 2) NULL,
[ac_PrimaryPEPMOther] [decimal](10, 2) NULL,
[ac_PrimaryPEPMOPGross] [decimal](10, 2) NULL,
[ac_PrimaryPEPMOPNet] [decimal](10, 2) NULL,
[ac_PrimaryPEPMGM] [decimal](10, 2) NULL,
[AC_PrimaryMAS90_OP] [varchar](8) NULL,
[AC_PrimaryMAS90_AF] [varchar](8) NULL,
[ac_PrimaryMas90] [varchar](8) NULL,
[ac_PrimaryMas90ClaimSettlement] [varchar](8) NULL,
[en_Primaryeffclio] [datetime] NULL,
[en_Primarytermclio] [datetime] NULL,
[en_PrimaryfNumberLives] [decimal](10, 2) NULL,
[Secondary Client] [varchar](35) NULL,
[Secondary Acct Mgr.] [varchar](35) NULL,
[Secondary Eff] [datetime] NULL,
[Secondary Term] [datetime] NULL,
[Secondary Address] [varchar](35) NULL,
[Secondary City] [varchar](35) NULL,
[Secondary State] [varchar](35) NULL,
[Secondary Zip] [varchar](35) NULL,
[Secondary County] [varchar](35) NULL,
[Secondary Phone] [varchar](24) NULL,
[Secondary Fax] [varchar](24) NULL,
[Secondary Email] [varchar](60) NULL,
[SecondaryProduct] [varchar](5) NULL,
[SecondaryNetwork] [varchar](5) NULL,
[ac_SecondaryPCTSavingsSecondary] [decimal](10, 2) NULL,
[ac_SecondaryPCTNetGross] [decimal](10, 2) NULL,
[ac_SecondaryPEPMOther] [decimal](10, 2) NULL,
[ac_SecondaryPEPMOPGross] [decimal](10, 2) NULL,
[ac_SecondaryPEPMOPNet] [decimal](10, 2) NULL,
[ac_SecondaryPEPMGM] [decimal](10, 2) NULL,
[AC_SecondaryMAS90_OP] [varchar](8) NULL,
[AC_SecondaryMAS90_AF] [varchar](8) NULL,
[ac_SecondaryMas90] [varchar](8) NULL,
[ac_SecondaryMas90ClaimSettlement] [varchar](8) NULL,
[en_Secondaryeffclio] [datetime] NULL,
[en_Secondarytermclio] [datetime] NULL,
[en_SecondaryfNumberLives] [decimal](10, 2) NULL,
[UtilizationTotalCharges] [decimal](10, 2) NULL,
[UtilizationSavings] [decimal](10, 2) NULL,
[claimcount] [int] NULL
)

Insert Into T_Primary_Secondary_Info([cli_name],[ClientNumberOrg],[Primary Client],[Primary Acct Mgr.],[Primary Eff],[Primary Term],[Primary Address],[Primary City],[Primary State],[Primary Zip],
[Primary County],[Primary Phone],[Primary Fax],[Primary Email],[PrimaryProduct],[PrimaryNetwork],[ac_PrimaryPCTSavingsSecondary],[ac_PrimaryPCTNetGross],
[ac_PrimaryPEPMOther],[ac_PrimaryPEPMOPGross],[ac_PrimaryPEPMOPNet],[ac_PrimaryPEPMGM],[AC_PrimaryMAS90_OP],[AC_PrimaryMAS90_AF],[ac_PrimaryMas90],
[ac_PrimaryMas90ClaimSettlement],[en_Primaryeffclio],[en_Primarytermclio],[en_PrimaryfNumberLives],[Secondary Client],[Secondary Acct Mgr.],
[Secondary Eff],[Secondary Term],[Secondary Address],[Secondary City],[Secondary State],[Secondary Zip],[Secondary County],[Secondary Phone],
[Secondary Fax],[Secondary Email],[SecondaryProduct],[SecondaryNetwork],[ac_SecondaryPCTSavingsSecondary],[ac_SecondaryPCTNetGross],
[ac_SecondaryPEPMOther],[ac_SecondaryPEPMOPGross],[ac_SecondaryPEPMOPNet],[ac_SecondaryPEPMGM],[AC_SecondaryMAS90_OP],
[AC_SecondaryMAS90_AF],[ac_SecondaryMas90],[ac_SecondaryMas90ClaimSettlement],[en_Secondaryeffclio],[en_Secondarytermclio],
[en_SecondaryfNumberLives],[UtilizationTotalCharges],[UtilizationSavings],[claimcount])

Select Distinct
[cli_name],[ClientNumberOrg],[Primary Client],[Primary Acct Mgr.],[Primary Eff],[Primary Term],[Primary Address],[Primary City],[Primary State],[Primary Zip],
[Primary County],[Primary Phone],[Primary Fax],[Primary Email],[PrimaryProduct],[PrimaryNetwork],[ac_PrimaryPCTSavingsSecondary],[ac_PrimaryPCTNetGross],
[ac_PrimaryPEPMOther],[ac_PrimaryPEPMOPGross],[ac_PrimaryPEPMOPNet],[ac_PrimaryPEPMGM],[AC_PrimaryMAS90_OP],[AC_PrimaryMAS90_AF],[ac_PrimaryMas90],
[ac_PrimaryMas90ClaimSettlement],[en_Primaryeffclio],[en_Primarytermclio],[en_PrimaryfNumberLives],[Secondary Client],[Secondary Acct Mgr.],
[Secondary Eff],[Secondary Term],[Secondary Address],[Secondary City],[Secondary State],[Secondary Zip],[Secondary County],[Secondary Phone],
[Secondary Fax],[Secondary Email],[SecondaryProduct],[SecondaryNetwork],[ac_SecondaryPCTSavingsSecondary],[ac_SecondaryPCTNetGross],
[ac_SecondaryPEPMOther],[ac_SecondaryPEPMOPGross],[ac_SecondaryPEPMOPNet],[ac_SecondaryPEPMGM],[AC_SecondaryMAS90_OP],
[AC_SecondaryMAS90_AF],[ac_SecondaryMas90],[ac_SecondaryMas90ClaimSettlement],[en_Secondaryeffclio],[en_Secondarytermclio],
[en_SecondaryfNumberLives],[UtilizationTotalCharges],[UtilizationSavings],[claimcount]

from [VW_Primary_Secondary_Info]

Where
([Primary Eff] >= @Primbottomdate and
[Primary Eff] <= @Primtopdate and
([PrimaryNetwork] IN (@PrimNetwork)) and
([PrimaryProduct] IN (@PrimProduct))) or

([Secondary Eff] >= @Primbottomdate and
[Secondary Eff] <= @Primtopdate and
([SecondaryNetwork] IN (@PrimNetwork)) and
([SecondaryProduct] IN (@PrimProduct)))

END

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-02-09 : 10:35:56

for some reason my paramters are not prompting me to enter any data in
The reason is: That's not possible

No, you're never too old to Yak'n'Roll if you're too young to die.

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2012-02-09 : 10:44:07

okay thanks, but what do I need to get these to work?

declare
@primbottomdate datetime,
@primtopdate datetime,
@PrimProduct Varchar (50),
@PrimNetwork Varchar (50),
@Secbottomdate datetime,
@Sectopdate datetime,
@SecProduct Varchar (50),
@SecNetwork Varchar (50)

quote:
Originally posted by webfred

for some reason my paramters are not prompting me to enter any data in
The reason is: That's not possible

No, you're never too old to Yak'n'Roll if you're too young to die.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-09 : 10:48:41

How are you trying to execute it? From SQL Server Management Studio query window? Or from a client program?

In SQL Server Management Studio, if you open up object explorer and navigate down to YourDabase -> Programmability -> Stored Procedures, find the stored proc name, right click on it and select "Execute Stored Procedure..." it should pop up a prompt window that lets you enter parameter values.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-02-09 : 10:50:09

It will work, it will just not prompt you for the values. This is SQL SERVER, not Crystal Reports.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

whatamouth
Starting Member

16 Posts

Posted - 2012-02-09 : 11:06:12

Declare them as parameters.

create procedure <procedure name> (@primbottomdate datetime,
@primtopdate datetime,
@PrimProduct Varchar (50),
@PrimNetwork Varchar (50),
@Secbottomdate datetime,
@Sectopdate datetime,
@SecProduct Varchar (50),
@SecNetwork Varchar (50))

as begin

if exists (
select *
from dbo.sysobjects
where id = object_id(N'[dbo].[T_Primary_Secondary_Info]')
and OBJECTPROPERTY(id, N'IsTable') = 1)
drop table [dbo].[T_Primary_Secondary_Info]

Create Table T_Primary_Secondary_Info(
[cli_name] [varchar](35) NULL,
[ClientNumberOrg] [varchar](20) NULL,
[Primary Client] [varchar...
...
...

end

Then execute it from the client app just like what sunita said.

Neil Matias
neilmatias@yahoo.com

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2012-02-09 : 11:07:40

Will I was testing it in SQL first, but then I am using the sp for my report in SQL Development Studios.

This is what I get when I execute the store procedure.

DECLARE @return_value int

EXEC @return_value = [dbo].[p_Primary_Secondary_Info]

SELECT 'Return Value' = @return_value

GO

whatamouth
Starting Member

16 Posts

Posted - 2012-02-09 : 11:26:22

create procedure [dbo].[p_Primary_Secondary_Info]
(
@primbottomdate datetime,
@primtopdate datetime,
@PrimProduct Varchar (50),
@PrimNetwork Varchar (50),
@Secbottomdate datetime,
@Sectopdate datetime,
@SecProduct Varchar (50),
@SecNetwork Varchar (50)
)

AS
Begin

if exists (
select *
from dbo.sysobjects
where id = object_id(N'[dbo].[T_Primary_Secondary_Info]')
and OBJECTPROPERTY(id, N'IsTable') = 1)
drop table [dbo].[T_Primary_Secondary_Info]

Create Table T_Primary_Secondary_Info(
[cli_name] [varchar](35) NULL,
[ClientNumberOrg] [varchar](20) NULL,
[Primary Client] [varchar](35) NULL,
[Primary Acct Mgr.] [varchar](35) NULL,
[Primary Eff] [datetime] NULL,
[Primary Term] [datetime] NULL,
[Primary Address] [varchar](35) NULL,
[Primary City] [varchar](35) NULL,
[Primary State] [varchar](35) NULL,
[Primary Zip] [varchar](35) NULL,
[Primary County] [varchar](35) NULL,
[Primary Phone] [varchar](24) NULL,
[Primary Fax] [varchar](24) NULL,
[Primary Email] [varchar](60) NULL,
[PrimaryProduct] [varchar](5) NULL,
[PrimaryNetwork] [varchar](5) NULL,
[ac_PrimaryPCTSavingsSecondary] [decimal](10, 2) NULL,
[ac_PrimaryPCTNetGross] [decimal](10, 2) NULL,
[ac_PrimaryPEPMOther] [decimal](10, 2) NULL,
[ac_PrimaryPEPMOPGross] [decimal](10, 2) NULL,
[ac_PrimaryPEPMOPNet] [decimal](10, 2) NULL,
[ac_PrimaryPEPMGM] [decimal](10, 2) NULL,
[AC_PrimaryMAS90_OP] [varchar](8) NULL,
[AC_PrimaryMAS90_AF] [varchar](8) NULL,
[ac_PrimaryMas90] [varchar](8) NULL,
[ac_PrimaryMas90ClaimSettlement] [varchar](8) NULL,
[en_Primaryeffclio] [datetime] NULL,
[en_Primarytermclio] [datetime] NULL,
[en_PrimaryfNumberLives] [decimal](10, 2) NULL,
[Secondary Client] [varchar](35) NULL,
[Secondary Acct Mgr.] [varchar](35) NULL,
[Secondary Eff] [datetime] NULL,
[Secondary Term] [datetime] NULL,
[Secondary Address] [varchar](35) NULL,
[Secondary City] [varchar](35) NULL,
[Secondary State] [varchar](35) NULL,
[Secondary Zip] [varchar](35) NULL,
[Secondary County] [varchar](35) NULL,
[Secondary Phone] [varchar](24) NULL,
[Secondary Fax] [varchar](24) NULL,
[Secondary Email] [varchar](60) NULL,
[SecondaryProduct] [varchar](5) NULL,
[SecondaryNetwork] [varchar](5) NULL,
[ac_SecondaryPCTSavingsSecondary] [decimal](10, 2) NULL,
[ac_SecondaryPCTNetGross] [decimal](10, 2) NULL,
[ac_SecondaryPEPMOther] [decimal](10, 2) NULL,
[ac_SecondaryPEPMOPGross] [decimal](10, 2) NULL,
[ac_SecondaryPEPMOPNet] [decimal](10, 2) NULL,
[ac_SecondaryPEPMGM] [decimal](10, 2) NULL,
[AC_SecondaryMAS90_OP] [varchar](8) NULL,
[AC_SecondaryMAS90_AF] [varchar](8) NULL,
[ac_SecondaryMas90] [varchar](8) NULL,
[ac_SecondaryMas90ClaimSettlement] [varchar](8) NULL,
[en_Secondaryeffclio] [datetime] NULL,
[en_Secondarytermclio] [datetime] NULL,
[en_SecondaryfNumberLives] [decimal](10, 2) NULL,
[UtilizationTotalCharges] [decimal](10, 2) NULL,
[UtilizationSavings] [decimal](10, 2) NULL,
[claimcount] [int] NULL
)

Insert Into T_Primary_Secondary_Info([cli_name],[ClientNumberOrg],[Primary Client],[Primary Acct Mgr.],[Primary Eff],[Primary Term],[Primary Address],[Primary City],[Primary State],[Primary Zip],
[Primary County],[Primary Phone],[Primary Fax],[Primary Email],[PrimaryProduct],[PrimaryNetwork],[ac_PrimaryPCTSavingsSecondary],[ac_PrimaryPCTNetGross],
[ac_PrimaryPEPMOther],[ac_PrimaryPEPMOPGross],[ac_PrimaryPEPMOPNet],[ac_PrimaryPEPMGM],[AC_PrimaryMAS90_OP],[AC_PrimaryMAS90_AF],[ac_PrimaryMas90],
[ac_PrimaryMas90ClaimSettlement],[en_Primaryeffclio],[en_Primarytermclio],[en_PrimaryfNumberLives],[Secondary Client],[Secondary Acct Mgr.],
[Secondary Eff],[Secondary Term],[Secondary Address],[Secondary City],[Secondary State],[Secondary Zip],[Secondary County],[Secondary Phone],
[Secondary Fax],[Secondary Email],[SecondaryProduct],[SecondaryNetwork],[ac_SecondaryPCTSavingsSecondary],[ac_SecondaryPCTNetGross],
[ac_SecondaryPEPMOther],[ac_SecondaryPEPMOPGross],[ac_SecondaryPEPMOPNet],[ac_SecondaryPEPMGM],[AC_SecondaryMAS90_OP],
[AC_SecondaryMAS90_AF],[ac_SecondaryMas90],[ac_SecondaryMas90ClaimSettlement],[en_Secondaryeffclio],[en_Secondarytermclio],
[en_SecondaryfNumberLives],[UtilizationTotalCharges],[UtilizationSavings],[claimcount])

Select Distinct
[cli_name],[ClientNumberOrg],[Primary Client],[Primary Acct Mgr.],[Primary Eff],[Primary Term],[Primary Address],[Primary City],[Primary State],[Primary Zip],
[Primary County],[Primary Phone],[Primary Fax],[Primary Email],[PrimaryProduct],[PrimaryNetwork],[ac_PrimaryPCTSavingsSecondary],[ac_PrimaryPCTNetGross],
[ac_PrimaryPEPMOther],[ac_PrimaryPEPMOPGross],[ac_PrimaryPEPMOPNet],[ac_PrimaryPEPMGM],[AC_PrimaryMAS90_OP],[AC_PrimaryMAS90_AF],[ac_PrimaryMas90],
[ac_PrimaryMas90ClaimSettlement],[en_Primaryeffclio],[en_Primarytermclio],[en_PrimaryfNumberLives],[Secondary Client],[Secondary Acct Mgr.],
[Secondary Eff],[Secondary Term],[Secondary Address],[Secondary City],[Secondary State],[Secondary Zip],[Secondary County],[Secondary Phone],
[Secondary Fax],[Secondary Email],[SecondaryProduct],[SecondaryNetwork],[ac_SecondaryPCTSavingsSecondary],[ac_SecondaryPCTNetGross],
[ac_SecondaryPEPMOther],[ac_SecondaryPEPMOPGross],[ac_SecondaryPEPMOPNet],[ac_SecondaryPEPMGM],[AC_SecondaryMAS90_OP],
[AC_SecondaryMAS90_AF],[ac_SecondaryMas90],[ac_SecondaryMas90ClaimSettlement],[en_Secondaryeffclio],[en_Secondarytermclio],
[en_SecondaryfNumberLives],[UtilizationTotalCharges],[UtilizationSavings],[claimcount]

from [VW_Primary_Secondary_Info]

Where
([Primary Eff] >= @Primbottomdate and
[Primary Eff] <= @Primtopdate and
([PrimaryNetwork] IN (@PrimNetwork)) and
([PrimaryProduct] IN (@PrimProduct))) or

([Secondary Eff] >= @Primbottomdate and
[Secondary Eff] <= @Primtopdate and
([SecondaryNetwork] IN (@PrimNetwork)) and
([SecondaryProduct] IN (@PrimProduct)))

END

Neil Matias
neilmatias@yahoo.com

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2012-02-09 : 13:03:09

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources