dynamic sql using exec

Question

Hello,

I have a script which is almost done, but with this problem which I cannot solve it. Can anyone help?

I have a table which will contain all the column names I have to use, and some of these columns will have start value, some of these columns will use the same value as that table.

For example:

ColumnName	DataType	StartValue
Col_1	varchar	‘T’
Col_2	int	10
Col_3	NULL	NULL
Col_4	NULL	NULL

I will based on StartValue to give column names. If it's int, I will use StartValue 10 plus the loop count since it will insert numbers of records based on it; if it's varchar, I will start with 'T' plus Col_1 value from the table; since NULL for StartValue, I will only select column values of Col_3 and Col_4.

I have a list which have select list which have variable inside it since I have to use loop count for it. The difficuty thing is int. How can I loop it, and add value to that StartValue everytime I execute it?

Answer 1

I am not quite understanding what you are asking for.  Can you provide a sample of the expected output?

---

http://jahaines.blogspot.com/

Answer 2

I want to run like below:

declare @List varchar(2000)

set @List = 'select ' + @select + ' from ' + @table + ' where ' + @where

which @select will be:
'T'+Col_1,10+@loopcount,Col_3,Col_4

@table will be a table name; @where will be ID in (1,2,3)

when I want to run exec (@List), it always shows me error as must declare @loopcount which I already defined and set value.

Answer 3

You do not have @Loopcount defined in the code you posted.  This procedure looks like a headache waiting to happen.  Why do you need the code to be so dynamic?  There is a lot of security risk and potential performance problems that go along with flexibilty and that should be accounted for. 

I really recommend that you read this post on dynamic sql, http://www.sommarskog.se/dynamic_sql.html

The solution I posted below is based on what I understand your problem to be the solution below should work.

SET NOCOUNT ON
GO

DECLARE @t TABLE(
id INT,
ColumnName CHAR(5),
DataType VARCHAR(15),
StartValue VARCHAR(15)
)

INSERT INTO @t VALUES (1,'Col_1','varchar','T');
INSERT INTO @t VALUES (1,'Col_2','INT','10');
INSERT INTO @t VALUES (1,'Col_3',NULL,NULL);
INSERT INTO @t VALUES (1,'Col_4',NULL,NULL);

DECLARE @Loopcount INT,
		@sql NVARCHAR(1000)
		
SET @Loopcount = 5
SELECT @sql = COALESCE(@sql + ', ','SELECT ') 
	+ CASE 
		WHEN DataType IN ('VARCHAR','CHAR','NVARCHAR','NCHAR')
		THEN COALESCE(StartValue,'') + COALESCE(ColumnName,'')
		WHEN DataType IN('INT','DECIMAL','NUMERIC','FLOAT','REAL')
		THEN CAST(CAST(COALESCE([StartValue],0)AS INT) + COALESCE(@Loopcount,0) AS VARCHAR(10))
	  ELSE [ColumnName]
	  END
FROM @t
WHERE id IN(1,2,3)

SET @sql = @sql + N' FROM @t --WHERE id IN(1,2,3)'

PRINT @sql
--EXEC sp_executesql @sql

---

http://jahaines.blogspot.com/

Answer 4

It seems that when I declare @loopcount as int, it cannot be directly inside of exec (). I now changed to  @select will be:
'T'+Col_1,ltrim(rtrim(str(10+@loopcount))),Col_3,Col_4, and then exec ().

But I got another problem, since I declare @loopcount first as int, and gave value as 1, it will always be 11 for Col_2 instead of 10+@loopcount which should be dynamic.

Any suggestion?

Answer 5

You have to cast or convert @Loopcount to a varchar before you can concatenate the value to the dynamic string.  You can reference my code above, on how to do this.


CAST(CAST(COALESCE([StartValue],0)AS INT) + COALESCE(@Loopcount,0) AS VARCHAR(10))

---

http://jahaines.blogspot.com/

Answer 6

I understand we have to convert to varchar. My question is after convert, it will only show the initial int value which is not correct since I have to depend on @loopcount to add value to the startvalue since it's a loop.

while

 

@loopcount <= @Records

begin

 

exec ('insert into temp ' +

 

' select ' + @Select + ' from ' + @TName + @Where)

 

set @loopcount = @loopcount + 1

end

and above is not working.

Answer 7

I understand we have to convert to varchar. My question is after convert, it will only show the initial int value which is not correct since I have to depend on @loopcount to add value to the startvalue since it's a loop.

while  

begin

 

 

 

end

and above is not working.

set @loopcount = @loopcount + 1exec ('insert into temp ' + ' select ' + @Select + ' from ' + @TName + @Where)@loopcount <= @Records

Because nothing is changing inside the WHILE loop except @loopcount.

---

Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com