Dynamically pass column name to SQL query

Question

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.

Read a little about sp_executesql

Giorgi Nakeuri
– Giorgi Nakeuri

2015-02-25 22:01:50 +00:00
Commented Feb 25, 2015 at 22:01 — Giorgi Nakeuri
– Giorgi Nakeuri, Commented Feb 25, 2015 at 22:01

Giorgi Nakeuri · Accepted Answer · 2015-02-25 22:25:50Z

1

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

edited Feb 25, 2015 at 22:25

answered Feb 25, 2015 at 22:11

Giorgi Nakeuri

35.9k8 gold badges50 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Giorgi Nakeuri Over a year ago

@Carlos, you could write directly

'Update Registry Set @Col_Name = LEFT (@Col_Name,CHARINDEX('','',@Col_Name)-1) + '','' + LEFT (@Col_Name,CHARINDEX('','',@Col_Name)-1)'

. Now you don't need replace for comma.

Collectives™ on Stack Overflow

Dynamically pass column name to SQL query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related