CODEWITHSUNDEEP
sqlsql-servert-sqlstored-proceduresdynamic-sql
Carlos
Carlos

Reputation: 377

Dynamically pass column name to SQL query

I was wondering if is possible to pass the column name of a table so I can create a dynamic query for my stored procedure. I will appreciate any help!

Good query:

SELECT
A1,
LEFT (A1,CHARINDEX(',',A1)-1) AS UPC,
SUBSTRING(A1,CHARINDEX(',', A1 , 1)  + 1,LEN(A1) - CHARINDEX(',', A1) - CHARINDEX(',', REVERSE(A1))) AS Date,
SUBSTRING(A1,CHARINDEX(',', A1 , 1)  + 7,LEN(A1) - CHARINDEX(',', A1) - CHARINDEX(',', REVERSE(A1))) AS Qty
FROM Registry Where Id=26

Output:

 A1              UPC      Date    Qty
-------------------------------------
CPU1,01/15,3     CPU1     01/15   3

This is what I have so far for the stored procedure

SP:

ALTER PROCEDURE [dbo].[SP_UPD_Compartment] 
@Id  int, 
@Col_Name char(40) 

AS
BEGIN
SET NOCOUNT ON;

SELECT
@Col_Name,
LEFT (@Col_Name,CHARINDEX(',',@Col_Name)-1) AS UPC,
SUBSTRING(@Col_Name,CHARINDEX(',', @Col_Name , 1)  + 1,LEN(@Col_Name) - CHARINDEX(',', @Col_Name) - CHARINDEX(',', REVERSE(@Col_Name))) AS Date,
SUBSTRING(@Col_Name,CHARINDEX(',', @Col_Name , 1)  + 7,LEN(@Col_Name) - CHARINDEX(',', @Col_Name) - CHARINDEX(',', REVERSE(@Col_Name))) AS Qty
FROM Registry Where Id=@Id

END

Error msg:

Msg 537, Level 16, State 2, Procedure SP_UPD_Compartment, Line 9
Invalid length parameter passed to the LEFT or SUBSTRING function.

Upvotes: 0

Views: 1310

Answers (1)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

I am typing from tab, so there may be some syntax mistakes, but i think you will get the idea. First you create a string with your statement and then replace substring '@Col_Name' with passed parameter to procedure. Then you execute that dynamic sql. You can also add Print @cmd before EXEC(@cmd) to see what actually will be executed.

ALTER PROCEDURE [dbo].[SP_UPD_Compartment] 
@Id  INT, 
@Col_Name CHAR(40) 

AS
BEGIN
SET NOCOUNT ON;

DECLARE @cmd NVARCHAR(4000) =
'SELECT
@Col_Name,
LEFT (@Col_Name,CHARINDEX('','',@Col_Name)-1) AS UPC,
SUBSTRING(@Col_Name,CHARINDEX('','', @Col_Name , 1)  + 1,LEN(@Col_Name) - CHARINDEX('','', @Col_Name) - CHARINDEX('','', REVERSE(@Col_Name))) AS Date,
SUBSTRING(@Col_Name,CHARINDEX('','', @Col_Name , 1)  + 7,LEN(@Col_Name) - CHARINDEX('','', @Col_Name) - CHARINDEX('','', REVERSE(@Col_Name))) AS Qty
FROM Registry WHERE Id=' + CAST(@Id AS NVARCHAR(20))

SET @cmd = REPLACE(@cmd, '@Col_Name', @Col_Name)
EXEC(@cmd)

END

Upvotes: 1

Related Questions