Jump to content

Stored Proc Parameters help


kreplech
 Share

Recommended Posts

so... more on stored procs for me...let's say i have a stored proc likeCREATE PROCEDURE sp_TEST @Status_ID int ASSELECT * FROM tbl_anyTable where Status_ID = @Status_IDGOhow to make this "... where Status_ID in (@Status_ID)" - meaning I'd like to send a series of Status_ID. I'm not sure what the SP would look like and I'm not sure how to send the param from C-Sharp.Thanks!M

Link to comment
Share on other sites

so, i've read that i should use the following function:CREATE FUNCTION fn_splitComaDelimVals (@ItemList NVARCHAR(4000))RETURNS @IDTable TABLE (Item int NOT NULL) AS BEGIN DECLARE @tempItemList NVARCHAR(4000) SET @tempItemList = @ItemList DECLARE @i INT DECLARE @Item NVARCHAR(4000) SET @tempItemList = REPLACE (@tempItemList, ' ', '') SET @i = CHARINDEX(',', @tempItemList) WHILE (LEN(@tempItemList) > 0) BEGIN IF @i = 0 SET @Item = @tempItemList ELSE SET @Item = LEFT(@tempItemList, @i - 1) INSERT INTO @IDTable(Item) VALUES(@Item) IF @i = 0 SET @tempItemList = '' ELSE SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i) SET @i = CHARINDEX(',', @tempItemList) END RETURNENDand then...declare @Status_ID varcharSET @Status_ID = '500,501,502,503,504,505'SELECT a.* from tbl_Test a inner join fn_splitComaDelimVals(@Status_ID) b on b.item = a.statusreturns nothing. i've been staring at this for a while now - just wondering if some fresh eyes could see the problem.any help is appreciated.thanks,M

Link to comment
Share on other sites

[...] returns nothing. i've been staring at this for a while now - just wondering if some fresh eyes could see the problem.any help is appreciated.thanks,M
This is the culprit:
declare @Status_ID varcharSET @Status_ID = '500,501,502,503,504,505'

@Status_ID will be set to "5" as it's not big enough to hold your string.Use NVARCHAR(4000) to match your function parameter:

declare @Status_ID NVARCHAR(4000)SET @Status_ID = '500,501,502,503,504,505'

Link to comment
Share on other sites

Good eye! Thanks for the help - worked like a charm.

This is the culprit:
declare @Status_ID varcharSET @Status_ID = '500,501,502,503,504,505'

@Status_ID will be set to "5" as it's not big enough to hold your string.Use NVARCHAR(4000) to match your function parameter:

declare @Status_ID NVARCHAR(4000)SET @Status_ID = '500,501,502,503,504,505'

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...