tbaseflug
asked on
Convert simple query to dyanmic SQL - results to table variable/temp table
OK - I have the below query - need to convert it into dynamic SQL with the @datStart and @datStop date time params as dynamic variables - also need to take the results and put them into a table variable or a temp table to be used later on in the sproc - any help would be greatly appreciated!
SELECT @datStart, @datStop
-- DROP TABLE #TEMP_ACCT DROP TABLE #TEMP_AVG
SELECT DISTINCT ACCT_NO INTO #TEMP_ACCT FROM data8.dbStPetes.dbo.tblHEAD
WHERE (ISNULL(DISCHARGE,ADMIT) >= @datStart) AND (ISNULL(DISCHARGE,ADMIT) <= @datStop)
SELECT DISTINCT DEPT_CODE, PROC_CODE,
CASE WHEN ISNULL(SUM(QTY),0) = 0 OR ISNULL(SUM(REV),0) =0 THEN 0
ELSE SUM(REV)/SUM(QTY)
END AS AVGREV
INTO #TEMP_AVG
FROM data8.dbStPetes.dbo.tblTRANS
WHERE ACCT_NO IN(SELECT ACCT_NO FROM #TEMP_ACCT)
GROUP BY DEPT_CODE, PROC_CODE
ummm...not sure what you mean. Ifyou just want to use params, then you don't need to use dynamic sql
I do not quite understand what is your question here.
Except that @datStart and @datStop should be stored procedure parameters your query is already dynamic and is having result in the temp table for later use.
Except that @datStart and @datStop should be stored procedure parameters your query is already dynamic and is having result in the temp table for later use.
ASKER
Well - the database name is ultimately going to be a param as well - that I know how to do but am horrible at getting the dates to work and in getting the results out of the dynamic query
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
OK - I took the above and plugged it in but am getting the following error:
(1 row(s) affected)
Msg 214, Level 16, State 3, Procedure sp_executesql, Line 1
Procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'.
(1 row(s) affected)
Msg 214, Level 16, State 3, Procedure sp_executesql, Line 1
Procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'.
DECLARE @dbName nvarchar(100) ,@datStart datetime , @datStop datetime, @cdmDate datetime
SET @dbName = 'dbStPetes'
SET @cdmDate = '11/15/2006'
DECLARE @dateTable TABLE
(
cdmType varchar(50), cdmDesc varchar(100), qtyDateFrom datetime, qtyDateThru datetime, priorDate datetime
)
INSERT INTO @dateTable
EXEC dbo.spWebCDMEditor_GetCDMInfo @dbName, @cdmDate
SET @datStart = (SELECT qtyDateFrom FROM @dateTable) -- begin discharge date
SET @datStop = (SELECT qtyDateThru FROM @dateTable) -- end discharge date
-- DROP TABLE #TEMP_ACCT DROP TABLE #TEMP_AVG
CREATE TABLE #TEMP_AVG (DEPT_CODE int , PROC_CODE int, AVGREV int )
DECLARE @nSql nvarchar(1000)
SELECT @nSql = ' SELECT DISTINCT ACCT_NO FROM data8.' +@dbName+ '.dbo.tblHEAD
WHERE (ISNULL(DISCHARGE,ADMIT) >= @datStart) AND (ISNULL(DISCHARGE,ADMIT) <= @datStop)'
SELECT @nSql = 'SELECT DISTINCT DEPT_CODE, PROC_CODE,
CASE WHEN ISNULL(SUM(QTY),0) = 0 OR ISNULL(SUM(REV),0) =0 THEN 0
ELSE SUM(REV)/SUM(QTY)
END AS AVGREV
FROM data8.' +@dbName+ '.dbo.tblTRANS
WHERE ACCT_NO IN('+@nSql + ')
GROUP BY DEPT_CODE, PROC_CODE'
INSERT INTO #TEMP_AVG
exec sp_ExecuteSQL @nSql, '@datStart datetime, @datStop datetime ', @datStart, @datStop
ASKER
this did it - just changed out end to: exec sp_ExecuteSQL @nSql, N'@datStart datetime, @datStop datetime ', @datStart, @datStop
change the last two lines like this
INSERT INTO #TEMP_AVG
exec sp_ExecuteSQL @nSql, N'@datStart datetime, @datStop datetime ', @datStart, @datStop
INSERT INTO #TEMP_AVG
exec sp_ExecuteSQL @nSql, N'@datStart datetime, @datStop datetime ', @datStart, @datStop