Stored Procedures using Variables

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

SQL Server 2005 Forums

Transact-SQL (2005)

Stored Procedures using Variables

Author

Topic

JezLisle
Posting Yak Master

132 Posts

Posted - 2010-06-08 : 09:22:28

How can I use variables in a Stored Procedure?

I need to use the IN (....) as per my code below

How can I run the SP using more than 1 value if needed to?


ALTER PROCEDURE [dbo].[ShowFTFDataPropCount]
	@pContractID INT
AS
BEGIN
SELECT FTF.PropID
	,FTF.CountJobNo
FROM tmpFTFCountJobs FTF
INNER JOIN tmpProperties P
ON P.PropID = FTF.PropID
WHERE P.ContractID IN (@pContractID)
END

I get an error
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '74'.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-08 : 09:27:15

1 Use split function and join with the table
2 or use


ALTER PROCEDURE [dbo].[ShowFTFDataPropCount]
	@pContractID INT
AS
BEGIN
SELECT FTF.PropID
	,FTF.CountJobNo
FROM tmpFTFCountJobs FTF
INNER JOIN tmpProperties P
ON P.PropID = FTF.PropID
WHERE ','+@pContractID+',' like '%,'+cast(P.ContractID as varchar(10))+',%'
END

Madhivanan

Failing to plan is Planning to fail

JezLisle
Posting Yak Master

132 Posts

Posted - 2010-06-08 : 10:40:56

Excellent, thanks.

When executing the SP, do I use EXEC dbo.ShowFTFDataPropCount('74','75') as normal or another way?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-08 : 10:42:41

EXEC dbo.ShowFTFDataPropCount('74,75')

Madhivanan

Failing to plan is Planning to fail

JezLisle
Posting Yak Master

132 Posts

Posted - 2010-06-08 : 10:54:42

I tried that and got the error

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '74,75'.

Where has it gone wrong?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-06-08 : 11:15:10

@pContractID should be a varchar datatype

Jim

Everyday I learn something that somebody else already knew

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-08 : 11:16:17

@pContractID should have datatype varchar(100)

Madhivanan

Failing to plan is Planning to fail

JezLisle
Posting Yak Master

132 Posts

Posted - 2010-06-08 : 11:19:33

Sorry to be a pest, but that still says

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '74,75'.


ALTER PROCEDURE [dbo].[ShowFTFDataPropCount]
	@pContractID VARCHAR(100)
AS
BEGIN
SELECT FTF.PropID
	,FTF.CountJobNo
FROM tmpFTFCountJobs FTF
INNER JOIN tmpProperties P
ON P.PropID = FTF.PropID
WHERE ','+@pContractID+',' LIKE '%,'+CAST(P.ContractID AS VARCHAR(100))+',%'
END


EXEC dbo.ShowFTFDataPropCount('74,75')

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-06-08 : 11:21:37

Doh! No '(' when calling a procedure

EXEC dbo.ShowFTFDataPropCount '74,75'

jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources