Problem with select statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Problem with select statement

Author

Topic

venkatakoti
Starting Member

1 Post

Posted - 2007-11-01 : 08:24:31

i am executing the select statement with dynamic values. but query is not executing and it is printing the dynamic values and the code is

BEGIN
DECLARE @cur_position INTEGER;
DECLARE @remainder VARCHAR(1000);
DECLARE @cur_string VARCHAR(1000);
DECLARE @delimiter_length TINYINT;
DECLARE @delimiter VARCHAR(20);
DECLARE @query VARCHAR(1000);
DECLARE @remainder_length TINYINT;
DECLARE @query_length TINYINT;

SET @delimiter=','
SET @remainder ='S0192,S0193,S0194';
SET @remainder_length=LEN(@remainder);
SET @delimiter_length =LEN(@delimiter);
SET @cur_position=1;
SET @query='';
WHILE (LEN(@remainder) > 0 AND @cur_position > 0)
BEGIN
SET @cur_position=CHARINDEX(@delimiter,@remainder);
IF (SELECT @cur_position) = 0
BEGIN
SET @cur_string = @remainder;
END
ELSE
BEGIN
SET @cur_string = LEFT(@remainder, @cur_position - 1);
END
IF (SELECT @query) =''
BEGIN
SET @query='SUM(CASE BatchId WHEN '+ @cur_string+' THEN Temp ELSE 0 END) AS '+ @cur_string ;
END
ELSE
BEGIN
SET @query=@query+',SUM(CASE BatchId WHEN '+ @cur_string +' THEN Temp ELSE 0 END) AS '+ @cur_string ;
END
SET @remainder = SUBSTRING(@remainder,@cur_position+1,@remainder_length );

END

SELECT DateTime,SUBSTRING(@query,@cur_position+1,@query_length) FROM Northwind.dbo.sampletwo GROUP BY DateTime;

END;

OUTPUT
---------------

10/31/2007 2:44:57 PM SUM(CASE BatchId WHEN S0192 THEN Temp ELSE 0 END) AS S0192,SUM(CASE BatchId WHEN S0193 THEN Temp ELSE 0 END) AS S0193,SUM(CASE BatchId WHEN S0194 THEN Temp ELSE 0 END) AS S0194

if you see above out put the select query is not executing

nr
SQLTeam MVY

12543 Posts

Posted - 2007-11-01 : 10:21:29

IF (SELECT @cur_position) = 0
s.b.
IF @cur_position = 0

>> if you see above out put the select query is not executing
yes it is. It outputs DateTime and a substring of @query

if you want to execute @query then you need something like (not sure what you aer trying to do)
declare @sql varchar(1000)
select @sql = 'SELECT DateTime,' + SUBSTRING(@query,@cur_position+1,@query_length) + ' FROM Northwind.dbo.sampletwo GROUP BY DateTime'
exec (@sql)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources