Solved

Convert simple query to dyanmic SQL - results to table variable/temp table

Posted on 2009-05-07
7
493 Views
Last Modified: 2012-05-06
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

Open in new window

0
Comment
Question by:tbaseflug
7 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 24326545
ummm...not sure what you mean.  Ifyou just want to use params, then you don't need to use dynamic sql
0
 
LVL 4

Expert Comment

by:bljak
ID: 24326559
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.
0
 

Author Comment

by:tbaseflug
ID: 24326568
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 24326729
Hello tbaseflug,


DECLARE @dbName nvarchar(100) ,@datStart datetime , @datStop datetime
SELECT @dbName ='data8'
SELECT @datStart = '2008-01-01' , @datStop = '2009-01-01'
-- 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 ' +@dbName+ '.dbStPetes.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 '+@dbName+'.dbStPetes.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
 

Regards,

Aneesh
0
 

Author Comment

by:tbaseflug
ID: 24326805
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'.

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

Open in new window

0
 

Author Closing Comment

by:tbaseflug
ID: 31579027
this did it - just changed out end to: exec sp_ExecuteSQL @nSql, N'@datStart datetime, @datStop datetime ', @datStart, @datStop
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24326878
change the last two lines like this

INSERT INTO #TEMP_AVG
exec sp_ExecuteSQL @nSql, N'@datStart datetime, @datStop datetime ', @datStart, @datStop
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql server stored procedure parameter 10 20
Need time in SQL 8 30
SQL Syntax 6 27
access to sql migration 5 19
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question