How to declare and pass a variable inside the SQL String when using Stored Procedure.

Question

Post reply

How to declare and pass a variable inside the SQL String when using Stored Procedure.

kk_tech

Default port

Points: 1492
More actions
February 8, 2017 at 7:40 pm

#340457

SP variable should be available inside the @Query String. See comment section. SP works but doesn't return anything since it not taking dynamic parameters. Please help
for example:
--EXEC spgetList 2
ALTER Procedure spGetList @ID INT
AS
--DECLARE @ID INT
DECLARE @Query VARCHAR(500)
BEGIN
IF object_ID('tempdb..#TempTable') IS NOT NULL
DROP TABLE #TempTable
CREATE TABLE #TempTable
(ID INT, ProductName VARCHAR(10))
INSERT INTO #TempTable
SELECT '1', 'Computer'
--SELECT * FROM #TempTable
--DECLARE @Query VARCHAR(500)
SELECT @query =
'
-- How to pass SP parameters to this level?
DECLARE @ID INT
SELECT ID,ProductName FROM #TempTable Where ID = @ID'
--SELECT @query
EXEC (@Query)
END

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

Use sp_executesql:

https://msdn.microsoft.com/en-us/library/ms188001.aspx

_____________
Code for TallyGenerator

kk_tech Default port Points: 1492 More actions · Answer 2

Sorry, it doesn't work using sp_executesql.
My basic question is I would like to pass parameter value to inner query string (@query)

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

That's exactly what sp_executesql is for. It's no good just saying it doesn't work. What did you try, and what happened - error message, unexpected results, something else?

John

kk_tech Default port Points: 1492 More actions · Answer 4

Thank John for your reply,
This is what I get when I use
exec sp_ExecuteSQL (@Query), @ID

Msg 214, Level 16, State 3, Procedure sp_executesql, Line 1 [Batch Start Line 1]

Procedure expects parameter '@params' of type 'ntext/nchar/nvarchar'.

Do I need to make any Changes, I am not getting it, Where do I make change, Please help.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

The error message tells you what you're doing wrong. Use the link that Sergiy posted to get the exact syntax and some use cases.

John

ScottPletcher SSC Guru Points: 101122 More actions · Answer 6

Since you're already using dynamic SQL, you can directly pass the value to the SQL command:

ALTER PROCEDURE spGetList
@ID INT
AS
--DECLARE @ID INT
DECLARE @Query VARCHAR(500)

IF object_ID('tempdb..#TempTable') IS NOT NULL
DROP TABLE #TempTable

CREATE TABLE #TempTable
(ID INT, ProductName VARCHAR(10))

INSERT INTO #TempTable
SELECT '1', 'Computer'

--SELECT * FROM #TempTable

--DECLARE @Query VARCHAR(500)
SELECT @query =
'SELECT ID,ProductName FROM #TempTable Where ID = ' + CAST(@ID AS varchar(10))
--SELECT @query

EXEC (@Query)

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

ScottPletcher - Thursday, February 9, 2017 8:01 AM
Since you're already using dynamic SQL, you can directly pass the value to the SQL command:
ALTER PROCEDURE spGetList
@ID INT
AS
--DECLARE @ID INT
DECLARE @Query VARCHAR(500)
IF object_ID('tempdb..#TempTable') IS NOT NULL
DROP TABLE #TempTable
CREATE TABLE #TempTable
(ID INT, ProductName VARCHAR(10))
INSERT INTO #TempTable
SELECT '1', 'Computer'
--SELECT * FROM #TempTable
--DECLARE @Query VARCHAR(500)
SELECT @query =
'SELECT ID,ProductName FROM #TempTable Where ID = ' + CAST(@ID AS varchar(10))
--SELECT @query
EXEC (@Query)

I dislike this option because even if it's not possible from this code, someone will think that it's fine to concatenate strings and that's an open door for SQL injection.
The OP needs to read about sp_executesql and understand how to call it to get reusable plans and increased protection.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

kk_tech Default port Points: 1492 More actions · Answer 8

Thanks John, and Thank you everyone,

I was able to solve the error after modifying and declaring variable when calling sp_ExecuteSQL

sp_ExecuteSQL @Query, N'@ID INT', @ID