Passing string to Stored Procedure
-
This is probably a 101.
I am trying to pass a string from an ASP.NET page to a stored procedure. How can I get SQL Server to realize that "str(@ad_id)" is a string that should have single quotes.
I must have tried everything but the right answer
Here is my code.
CREATE PROCEDURE dbo.GetMember
@ad_id as varchar(20),
@dept_id as varchar(20)
AS
DECLARE @SQLStatement as varchar(2000)
SET @SQLStatement = 'SELECT DISTINCT members.ad_id FROM departments
SET @SQLStatement = @SQLStatement + ' WHERE departments.mgr_ad_id = ''' + str(@ad_id) + '''''
EXEC(@SQLStatement)
You help will save me some hair loss.
Tom
-
Hello Tom,
Can you try these lines by replacing them in your code:
SET @SQLStatement = 'SELECT DISTINCT members.ad_id FROM departments'
SET @SQLStatement = @SQLStatement + ' WHERE departments.mgr_ad_id = ' + '''@ad_id'''
Thanks and have a nice day!!!
Lucky -
This is what does work...
mgr_ad_id = ' + ''''+@ad_id+''''
thank for your help lucky... you pushed me in the right direction.
-
How about??
CREATE PROCEDURE dbo.GetMember @ad_id as varchar(20)
AS
SELECT DISTINCT members.ad_id FROM departments WHERE departments.mgr_ad_id = @ad_id
-
Great catch...maybe a link to the most read article on dynamic sql should be posted here.
btw..@dept_id is not being used!
ps:we used to have a member called remi who was just as quick & astute as you..wouldn't be related by any chance would you...
**ASCII stupid question, get a stupid ANSI !!!** -
Don't know him... maybe I'll ask my ubiquitus spook.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply