selecting result of a dynamic sql statemnt into a variable
-
'SELECT MAX(ID1) FROM (SELECT TOP '+ CAST(@grade1 as varchar)+ ' * FROM table1
WHERE device_date> CAST('''+CAST(@device_date as varchar)+ ''' AS datetime)
AND university_id= '''+ @university_id+''' AND college_id= '''+@college_id +'''
AND department_id= '''+@department_id+'''ORDER BY device_date) a'
I want to select the result of the above query into a variable
@no1 of datatype int
How can I do that. I tried the following statement and its not working for me.
'SELECT @no1=MAX(ID1) FROM (SELECT TOP '+ CAST(@grade1 as varchar)+ ' * FROM table1
WHERE device_date> CAST('''+CAST(@device_date as varchar)+ ''' AS datetime)
AND university_id= '''+ @university_id+''' AND college_id= '''+@college_id +'''
AND department_id= '''+@department_id+'''ORDER BY device_date) a'
Any Iddeas would be greately appreciated.
Thanks.
-
Do you need to select the max id from a dynamic sampling of the table?
-
I have had similar problems. You could put it into a #TempTable or @TableVariable and use sp_executesql. The problem often lies in the new SPID that is created when Dynamic SQL is used...
I wasn't born stupid - I had to study.
-
There's no need for dynamic sql in this situation... I'm just waiting for the answer to demonstrate
. -
yes ,I need to select the result(max(id)) into a variable. Also I have one more dynamic sql which returns onother value. So I am selecting these two results into two local variables and then comparing these two variables and then depending on the conditions I will do some DMLs on the table.
-
can you post the whole proc... maybe we can simplify it somehow.
-
DROP TABLE #temp
DECLARE @university_id varchar(8),
@college_id varchar(8),
@department_id varchar(4),
@status varchar(20),
@high_mark int,
@low_mark int,
@exam_date datetime,
@sqlselect1 varchar(1000),
@Sqlselect2 varchar(1000),
@id1 int,
@id2 int
SELECT IDENTITY (int,1,1) AS id1,a.university_id,a.college_id,a.department_id,a.exam_date, 'Status'=
CASE WHEN electric_power >b.grade_level2 then 'pass'
WHEN electric_power <b.grade_level1 then 'fail'
END,electric_power,grade_level2,grade_level1
INTO #temp
FROM spr_real_time_data a INNER JOIN spr_department b
ON a.university_id=b.university_id AND a.college_id=b.college_id AND a.department_id=b.department_id
WHERE a.department_id='1805' AND exam_date CAST('''+CAST(@exam_date as varchar)+ ''' AS datetime)
AND university_id= '''+ @university_id+''' AND college_id= '''+@college_id +'''
AND department_id= '''+@department_id+'''ORDER BY exam_date ) a1
WHERE id1+1 NOT IN(SELECT ID1 FROM (SELECT TOP '+ CAST(@high_mark as varchar)+ '
* FROM #temp WHERE exam_date> CAST('''+CAST(@exam_date as varchar)+ ''' AS datetime)
AND university_id= '''+ @university_id+''' AND college_id= '''+@college_id +'''
AND department_id= '''+@department_id+'''ORDER BY exam_date )a)'
EXEC (@Sqlselect1)
SELECT @sqlselect2='SELECT MAX(ID1) FROM (SELECT TOP '+ CAST(@high_mark as varchar)+ ' * FROM #temp
WHERE exam_date> CAST('''+CAST(@exam_date as varchar)+ ''' AS datetime)
AND university_id= '''+ @university_id+''' AND college_id= '''+@college_id +'''
AND department_id= '''+@department_id+'''ORDER BY exam_date) a'
SELECT @sqlselect2 as sqlstmt1
EXEC (@sqlselect2)
IF @id1<=@id2
BEGIN
INSERT INTO spr_grade_log(department_id,college_id,university_id,grade_condition,grade_time)
VALUES(@department_id,@college_id,@university_id,@status,@exam_date)
END
---------------------------------
END
FETCH NEXT FROM gradeCur
INTO @university_id,@college_id,@department_id,@high_mark,@low_mark,@status,@exam_date
END
DEALLOCATE gradeCur
GO
I was talking abt the dotted part. And sorry for not commenting the proc.
Thanks.
-
Can you describe what's this supposed to do...
I'll check your answer before I got to bed (6-7 hours)
-
HI Remi,
WE are collecting the department info.
Every department was set two electric power levels one High and the other low. Suppose if The electric power is beyond the higher level and it would be considered as 'pass' and if the electric power is below the lower level it should be considered as 'fail'.
Suppose if it became pass on day9, we should check status for the next 5 days and if its pass during the next 5 days it should be passed otherwise we dont mark anything on that. same with failure.. We need to check five consecutive days.
The description might be confusing coz i wrote this in a hurry.. I would I appreciate if u can help me in optimizing code. Before that can u let me know how to select the result into a variable
in the dynamic sql.
Thanks.
-
This is way more complexe than I expected... Will talk to you again on Monday (when I have access to a server).
-
Here's a quick and dirty example of how to select the result of dynamic sql into a variable. You need to wrap the sql statement in a temporary stored procedure with an output parameter. The reason this works is that the temporary stored procedure is local to the connection, not the batch like the sql statement in EXEC (@sql). You can also call EXEC sp_executesql (see KB262499).
DECLARE @S1 VARCHAR(8000)
SET @S1 = 'CREATE PROCEDURE #T1 (@V1 DATETIME OUTPUT) AS SELECT @V1 = GETDATE()'
EXEC (@S1)
DECLARE @V1 DATETIME EXEC #T1 @V1 OUTPUT SELECT @V1
DROP PROCEDURE #T1
GO
I verified that this technique also works within another procedure:
CREATE PROCEDURE #T2 AS
DECLARE @S1 VARCHAR(8000)
SET @S1 = 'CREATE PROCEDURE #T1 (@V1 DATETIME OUTPUT) AS SELECT @V1 = GETDATE()'
EXEC (@S1)
DECLARE @V1 DATETIME EXEC #T1 @V1 OUTPUT SELECT @V1
DROP PROCEDURE #T1
GO
EXEC #T2
GO
DROP PROCEDURE #T2
GO
Brian
MCDBA, MCSE+I, Master CNE -
Someone mentioned using a temp table, but didn't give an example.
use Northwind
declare @i int,@sql nvarchar(2000)
create table #tmp (i int)
set @i=-1
set @sql = 'declare @i int
SELECT @i=MAX(CategoryID) FROM dbo.Categories
where CategoryID in (SELECT TOP 5 CategoryID FROM dbo.Categories)
insert into #tmp (i) select @i'
exec (@sql)
SELECT @i= i from #tmp
print 'test ' + cast(@i as varchar)
drop table #tmp
-
Looks like I've got imbedding a variable into the script on the brain. Here is a different example with a more complex selection logic, but no variables.
use Northwind
declare @i int,@sql varchar(2000)
create table #tmp (i int)
set @i=-1
set @sql = 'insert into #tmp (i)
SELECT MAX(CategoryID) FROM dbo.Categories
where CategoryID in
(SELECT TOP 3 CategoryID FROM dbo.Categories
where CategoryName like ''%o%''
order by CategoryName desc)'
exec (@sql)
SELECT @i= i from #tmp
print 'test ' + cast(@i as varchar)
drop table #tmp
-
-- I hope this demonstrates a relatively simple method
declare @id1 int
create table #result (id1 int)
set @sql = 'SELECT MAX(ID1) FROM (SELECT TOP '+ CAST(@grade1 as varchar)+ ' * FROM table1
WHERE device_date> CAST('''+CAST(@device_date as varchar)+ ''' AS datetime)
AND university_id= '''+ @university_id+''' AND college_id= '''+@college_id +'''
AND department_id= '''+@department_id+'''ORDER BY device_date) a'
insert into #result (id1)
exec (@sql)
select @id1 = id1
from #result
select @id1
-
Hi Sahana,
In your original post you asked about selecting the result of a dynamic query into a variable. Here is an example using sp_executesql
DECLARE @no1 INT
SET @SQL = N'SET @no1 = (SELECT something FROM table)'
EXEC sp_executesql @SQL, N'@no1 INT OUTPUT', @no1 OUTPUT
Assuming the sql in your original post is correct and returns the value you require, something along these lines should return your value from dynamic sql:-
DECLARE @SQL NVARCHAR(4000)
DECLARE @no1 INT
SET @SQL = N'SET @no1 = (SELECT MAX(ID1) FROM (SELECT TOP '+ CAST(@grade1 AS VARCHAR(50)) + ' * FROM table1 WHERE device_date> CAST(''' + CAST(@device_date AS VARCHAR(50)) + ''' AS datetime) AND university_id= ' + CAST(@university_id AS VARCHAR(50)) + ' AND college_id= ' + CAST(@college_id AS VARCHAR(50)) + ' AND department_id= ' + cast(@department_id AS VARCHAR(50)) + ' ORDER BY device_date))'
EXEC sp_executesql @SQL, N'@no1 INT OUTPUT', @no1 OUTPUT
You do really need to understand Dynamic SQL before deciding to use it in anger. Read http://www.sommarskog.se/dynamic_sql.html and do some research.
I do believe Remi will give you a much better set based solution if you give him the time.
Sorry Remi - just answering the original question....... 
Have fun
Steve
We need men who can dream of things that never were.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply