SQL Stored Proc - Passing parameter for IN clause 1

[DH]

Hello,

Need help passing in the following string of fileID numbers to use in the stored procedure shown below. I would like the stored proc to return information for all users with the following fileID numbers (these fileID numbers are dynamically generated so I need to pass in on the fly)

10, 11, 15, 22, 30

CREATE PROCEDURE [dbo].[Read_Address_List]
@fileIDs varchar
AS
SELECT  firstName, lastName, address, city, state, zip
FROM    users                      
WHERE   fileId IN (@fileIDs)
GO

I eventually will pass in the parameter using asp.net / vb.net but for now, I am testing using query analyzer like so:

exec Read_Address_List 10, 11, 15, 22, 30

The is doesn't work because SQL interprets that I am passing in too many parameters.

What is the correct syntax to use?

Thank you,

DH

 

[DotNetGnat]

declare @blah as varchar(50)

@blah='10, 11, 15, 22, 30'


exec Read_Address_List @blah

-DNG

 

[SQLDenis]

you will nedd dynamic SQL

CREATE PROCEDURE [dbo].[Read_Address_List]
declare @fileIDs varchar(20)
--select @fileIDs ='10, 11, 15, 22, 30'
AS
declare @SQL varchar(500)
select @SQL ='SELECT firstName, lastName, address, city, state, zip
FROM users
WHERE fileId IN (' + @fileIDs + ')'

exec( @SQL )

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

While you are modifying this procedure as suggested, I suggest you change the procedure header, as well

CREATE PROCEDURE [dbo].[Read_Address_List]
@fileIDs varchar[red](8000)[/red]

When you use a varchar data type, you should get in to the habit of specifying the length of it.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[DH]

Thank you everyone for your help so far.

Here is what I have come up with so far:

I build the SQL Select statement dynamically in asp.net/vb.net and pass it to the following stored proc:

CREATE PROCEDURE [dbo].[Read_Address_List]
@strSQL nvarchar(500)
AS
exec(@strSQL)
GO

This works fine except that I need to grant SELECT permissions on 3 tables that the SELECT statement passed to the stored proc touches. I assigned the Read_Address_List stored proc EXECUTE permission. Isn't this sufficient for permissions? I really do not want to grant SELECT permission on the tables?

Misc: For what its worth, I am using this stored proc to generate a report using ActiveReports.Net 2.0 which has a funny way of passing parameters to stored procs...

Any additional suggestions?

DH

 

[ESquared]

Don't use dynamic SQL for this, PLEASE.

You can consult faq183-5207 for parsing a comma-delimited list. Or, since that FAQ is a little outdated now, for a much deeper look at the subject, see the fixed-length array elements section of Arrays and Lists in SQL Server by Erland Sommerskag, SQL Server MVP.

I don't use any other method any more besides fixed-length. It's by far the fastest, and quite clean to use. I never got around to updating that FAQ. Oh well.

 

[DH]

Thanks ESquared, I will check out the fixed-length array link now!

DH

 

[DH]

Thanks ESquared,

I ended up using the link you provided above along with

http://www.4guysfromrolla.com/webtech/031004-1.shtml

to arrive at a solution.

I created the following User Defined Function (UDF):

	CREATE FUNCTION dbo.Split
	(
		@List nvarchar(2000),
		@SplitOn nvarchar(5)
	)  
	RETURNS @RtnValue table 
	(
		
		Id int identity(1,1),
		Value nvarchar(100)
	) 
	AS  
	BEGIN 
		While (Charindex(@SplitOn,@List)>0)
		Begin

			Insert Into @RtnValue (value)
			Select 
				Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))

			Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
		End

		Insert Into @RtnValue (Value)
		Select Value = ltrim(rtrim(@List))
	
		Return
	END

This function splits the comma delimited string of ID numbers that is passed into the proc.

Works very nice!

For anyone interested the 2 links discussed in this post and ESquared's post contain the examples of putting to use.

Thanks again...

DH

 

[SQLSister]

DH, just so you know, when you use dynamic SQL you must set permissions at the table level. This is one of the reasons why dynamic SQL is to be avoided if possible.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[ESquared]

DH,

For what it's worth, the method you're using now is probably slower than the one in the FAQ I referenced.

Assigning string values to new string values as your function is doing is generally slower (in various programming languages) than using substrings to find the next occurrence.