Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Dynamic SQL Problem - hopefully easy one for a SQL Developer.

Posted on 2003-11-12
7
Medium Priority
?
597 Views
Last Modified: 2012-06-21
Dynamic SQL Statement not working - strange problem because if I PRINT the dynamic SQL and run it Query Analyser (or another sp) it works fine.
Please help, should be extremely easy for an experienced developer.

Also, do I need to cast @startDate and @endDate to VARCHAR in the dynamic sql to ensure all statements are of type string.

Cheers.



-----------------------------------------------------------------------
CREATE PROCEDURE spGetCoachCommentList

      @s varchar(50) = NULL,
      @expertTeam int = NULL,
      @startDate smalldatetime = NULL,
      @endDate smalldatetime = NULL

AS

      CREATE TABLE #tempTable(
            [id] int,
            [publishDate] smalldatetime,
            [title] varchar(75)
      )      

      -- create sql string
      DECLARE @mySQL varchar(1000)

      -- create search text
      DECLARE @likeText varchar(100)
      SET @likeText = '%' + @s + '%'

      -- get all article records
      SET @mySQL = 'INSERT INTO #tempTable ([id],publishDate,titel) SELECT t.[id],t.publishDate,ta.title + ''[article: '' + CAST(t.article AS VARCHAR) + '']'' AS title FROM tblCoachComments t, tblTrainingArticles ta WHERE t.article = ta.[id]'      
      IF (@s <> NULL) OR LEN(@s)>0
            SET @mySQL = @mySQL + ' AND ( ta.title LIKE ''' + @likeText + ''' OR ta.shortContent LIKE ''' + @likeText + ''' OR ta.[content] LIKE ''' + @likeText + ''')'
      IF (@expertTeam <> NULL) OR (@expertTeam > 0)
            SET @mySQL = @mySQL + ' AND ( t.expertTeam = ' + CAST(@expertTeam AS VARCHAR) +' )'
      IF (@startDate <> NULL)  OR (@startDate >= GETDATE())
            SET @mySQL = @mySQL + ' AND ( t.publishDate >= ' + CAST(@startDate AS VARCHAR) +' )'
      IF (@endDate <> NULL)  OR (@endDate >= GETDATE())
            SET @mySQL = @mySQL + ' AND ( t.publishDate <= ' + CAST(@endDate AS VARCHAR) +' )'
      EXECUTE @mySQL
      
      -- get all tip records
      SET @mySQL = 'INSERT INTO #tempTable SELECT t.[id],t.publishDate,ta.title + ''tip: '' + CAST(t.tip AS VARCHAR) + '']'' AS title FROM tblCoachComments t, tblTrainingTips ta WHERE t.tip = ta.[id]'
      IF (@s <> NULL) OR LEN(@s)>0
            SET @mySQL = @mySQL + ' AND ( ta.title LIKE ''' + @likeText + ''' OR ta.shortContent LIKE ''' + @likeText + ''' OR ta.[content] LIKE ''' + @likeText + ''' )'
      IF (@expertTeam <> NULL) OR (@expertTeam > 0)
            SET @mySQL = @mySQL + ' AND ( t.expertTeam = ' + CAST(@expertTeam AS VARCHAR) +' )'
      IF (@startDate <> NULL)  OR (@startDate >= GETDATE())
            SET @mySQL = @mySQL + ' AND ( t.publishDate >= ' + CAST(@startDate AS VARCHAR) +' )'
      IF (@endDate <> NULL)  OR (@endDate >= GETDATE())
            SET @mySQL = @mySQL + ' AND ( t.publishDate <= ' + CAST(@endDate AS VARCHAR) +' )'
      EXECUTE @mySQL
      
      -- get all website records
      SET @mySQL = 'INSERT INTO #tempTable SELECT t.[id],t.publishDate,ta.title + ''[website: + t.url + ]'' FROM AS title tblCoachComments WHERE t.url <> NULL'
      IF (@s <> NULL) OR LEN(@s)=0
            SET @mySQL = @mySQL + ' AND ( t.title LIKE ''' + @likeText + ''' OR t.shortDescription LIKE ''' + @likeText + ''' OR t.[description] LIKE ''' + @likeText + ''' )'
      IF (@expertTeam <> NULL) OR (@expertTeam > 0)
            SET @mySQL = @mySQL + ' AND ( t.expertTeam = ' + CAST(@expertTeam AS VARCHAR) +' )'
      IF (@startDate <> NULL)  OR (@startDate >= GETDATE())
            SET @mySQL = @mySQL + ' AND ( t.publishDate >= ' + CAST(@startDate AS VARCHAR) +' )'
      IF (@endDate <> NULL)  OR (@endDate >= GETDATE())
            SET @mySQL = @mySQL + ' AND ( t.publishDate <= ' + CAST(@endDate AS VARCHAR) +' )'
      EXECUTE @mySQL
      
      -- get all standalone records
      SET @mySQL = 'INSERT INTO #tempTable SELECT t.[id],t.publishDate,ta.title FROM tblCoachComments WHERE ( (t.article IS NULL) AND (t.tip IS NULL) AND (t.url IS NULL) )'
      IF (@s <> NULL) OR LEN(@s)=0
            SET @mySQL = @mySQL + ' AND ( t.title LIKE ''' + @likeText + ''' OR t.shortDescription LIKE ''' + @likeText + ''' OR t.[description] LIKE ''' + @likeText + '" )'
      IF (@expertTeam <> NULL) OR (@expertTeam > 0)
            SET @mySQL = @mySQL + ' AND ( t.expertTeam = ' + CAST(@expertTeam AS VARCHAR) +' )'
      IF (@startDate <> NULL)  OR (@startDate >= GETDATE())
            SET @mySQL = @mySQL + ' AND ( t.publishDate >= ' + CAST(@startDate AS VARCHAR) +' )'
      IF (@endDate <> NULL)  OR (@endDate >= GETDATE())
            SET @mySQL = @mySQL + ' AND ( t.publishDate <= ' + CAST(@endDate AS VARCHAR) +' )'
      EXECUTE @mySQL
      
      -- now get the ordered records
      SET @mySQL = 'SELECT t.[id], t.title FROM #tempTable t ORDER BY t.publishDate DESC'
      EXECUTE @mySQL
      
      -- turn on back on rows affected
      SET NOCOUNT OFF

      -- drop table
      DROP TABLE #tempTable
      
      /*

      -- get all article records
       INSERT INTO #tempTable
      SELECT
            t.[id],
            t.publishDate,
            ta.title + ' [article: ' + CAST(t.article AS VARCHAR) + ']'
      FROM
            tblCoachComments t
            INNER JOIN tblTrainingArticles ta ON t.article = ta.[id]      

      -- get all tip records
      INSERT INTO #tempTable
      SELECT
            t.[id],
            t.publishDate,
            ta.title + ' [tip: ' + CAST(t.tip AS VARCHAR) + ']'
      FROM
            tblCoachComments t
            INNER JOIN tblTrainingTips ta ON t.tip = ta.[id]      

      -- get all website records
      INSERT INTO #tempTable
      SELECT
            t.[id],
            t.publishDate,
            ta.title + ' [website: ' + t.url  + ']'
      FROM
            tblCoachComments t
      WHERE
            t.url <> NULL

      -- get all standalone records
      INSERT INTO #tempTable
      SELECT
            t[id],
            t.publishDate,
            t.title
      FROM
            tblCoachComments
      WHERE
            (t.article IS NULL OR t.article < 1) AND
            (t.tip IS NULL OR t.tip < 1) AND
            (t.url IS NULL OR LEN(t.url)=0)


      -- NOW, order records
      SELECT
            t.[id],
            t.title
      FROM
            #tempTable t
      ORDER BY
            t.publishDate DESC
 
      -- turn on back on rows affected
      SET NOCOUNT OFF

      -- drop table
      DROP TABLE #tempTable

*/
GO
-----------------------------------------------------------------------

CREATE TABLE [dbo].[tblCoachComments] (
      [id] [int] NOT NULL ,
      [publishDate] [smalldatetime] NOT NULL ,
      [article] [int] NULL ,
      [tip] [int] NULL ,
      [title] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [shortDescription] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [description] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [url] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [expertTeam] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblTrainingArticles] (
      [id] [int] IDENTITY (1, 1) NOT NULL ,
      [publishDate] [smalldatetime] NOT NULL ,
      [category] [int] NOT NULL ,
      [title] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [shortDescription] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [totalHits] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblTrainingTips] (
      [id] [int] IDENTITY (1, 1) NOT NULL ,
      [publishDate] [smalldatetime] NOT NULL ,
      [category] [int] NOT NULL ,
      [title] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [shortDescription] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [description] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [totalHits] [int] NULL
) ON [PRIMARY]
GO

-------------------------------------------
0
Comment
Question by:smacca
  • 3
  • 2
5 Comments
 
LVL 19

Expert Comment

by:Rimvis
ID: 9738093
Which dynamic SQL doesn't work? There are 3 INSERT and 1 SELECT operations in your SP.

This statement has absolutely no efect, btw, as you select data "to nowhere":
     -- now get the ordered records
     SET @mySQL = 'SELECT t.[id], t.title FROM #tempTable t ORDER BY t.publishDate DESC'
     EXECUTE @mySQL

And yes, you must  cast all non-string data types to string, when building dynamic SQL,
0
 

Author Comment

by:smacca
ID: 9738323
It stops at the first insert statement saying "identifier
Check it out by running spGetCoachCommentList in Query Analyser.

--------------------
Server: Msg 203, Level 16, State 2, Procedure spGetCoachCommentList, Line 33
The name 'INSERT INTO #tempTable ([id],publishDate,titel) SELECT t.[id],t.publishDate,ta.title + '[article: ' + CAST(t.article AS VARCHAR) + ']' AS title FROM tblCoachComments t, tblTrainingArticles ta WHERE t.article = ta.[id]' is not a valid identifier.
-------------------------------

The part I cant workout is if you take that statement (and add create table so it compiles and runs)
 
CREATE TABLE #tempTable(
          [id] int,
          [publishDate] smalldatetime,
          [title] varchar(75)
     )    
-- this is the EXACT! copy of the SQL returned by error
INSERT INTO #tempTable ([id],publishDate,titel) SELECT t.[id],t.publishDate,ta.title + '[article: ' + CAST(t.article AS VARCHAR) + ']' AS title FROM tblCoachComments t, tblTrainingArticles ta WHERE t.article = ta.[id]

DROP #tempTable

Everything works fine!

Lastly, regarding your point, you are correct it does do nothing - lol! It should not be dynamic sql, it should read:

SELECT
  t.[id],
  t.title
FROM
  #tempTable t
ORDER BY
  t.publishDate DESC
0
 

Author Comment

by:smacca
ID: 9738431
ANSWER: The execute statement must be contained in brackets otherwise string is not encapsulated.

EXECUTE (@mySQL)
0
 
LVL 19

Expert Comment

by:Rimvis
ID: 9738435
>> The part I cant workout is if you take that statement (and add create table so it compiles and runs)
Yes, I missed that, sorry. Too much code, I gues ;o)

INSERT INTO statement can't insert data into table that doesn't exist. SELECT .. INTO, in other hand, does.
So statement like this will work without explicitly creating table.

SELECT t.[id],t.publishDate,ta.title + '[article: ' + CAST(t.article AS VARCHAR) + ']' AS title FROM tblCoachComments t, tblTrainingArticles ta WHERE t.article = ta.[id]
 INTO #tempTable

Of course, you must execute DROP #tempTable.
0
 

Accepted Solution

by:
smacca earned 0 total points
ID: 9738437
CREATE PROCEDURE spGetCoachCommentList

      @s varchar(50) = NULL,
      @expertTeam int = NULL,
      @startDate smalldatetime = NULL,
      @endDate smalldatetime = NULL

AS

      CREATE TABLE #tempTable(
            [id] int,
            [publishDate] smalldatetime,
            [title] varchar(75)
      )      

      -- create sql string
      DECLARE @mySQL varchar(1000)

      -- create search text
      DECLARE @likeText varchar(100)
      SET @likeText = '%' + @s + '%'

      -- get all article records
      SET @mySQL = 'INSERT INTO #tempTable ([id],publishDate,titel) SELECT t.[id],t.publishDate,ta.title + ''[article: '' + CAST(t.article AS VARCHAR) + '']'' AS title FROM tblCoachComments t, tblTrainingArticles ta WHERE t.article = ta.[id]'      
      IF (@s <> NULL) OR LEN(@s)>0
            SET @mySQL = @mySQL + ' AND ( ta.title LIKE ''' + @likeText + ''' OR ta.shortContent LIKE ''' + @likeText + ''' OR ta.[content] LIKE ''' + @likeText + ''')'
      IF (@expertTeam <> NULL) OR (@expertTeam > 0)
            SET @mySQL = @mySQL + ' AND ( t.expertTeam = ' + CAST(@expertTeam AS VARCHAR) +' )'
      IF (@startDate <> NULL)  OR (@startDate >= GETDATE())
            SET @mySQL = @mySQL + ' AND ( t.publishDate >= ' + CAST(@startDate AS VARCHAR) +' )'
      IF (@endDate <> NULL)  OR (@endDate >= GETDATE())
            SET @mySQL = @mySQL + ' AND ( t.publishDate <= ' + CAST(@endDate AS VARCHAR) +' )'
      EXECUTE (@mySQL)
      
      -- get all tip records
      SET @mySQL = 'INSERT INTO #tempTable SELECT t.[id],t.publishDate,ta.title + ''tip: '' + CAST(t.tip AS VARCHAR) + '']'' AS title FROM tblCoachComments t, tblTrainingTips ta WHERE t.tip = ta.[id]'
      IF (@s <> NULL) OR LEN(@s)>0
            SET @mySQL = @mySQL + ' AND ( ta.title LIKE ''' + @likeText + ''' OR ta.shortContent LIKE ''' + @likeText + ''' OR ta.[content] LIKE ''' + @likeText + ''' )'
      IF (@expertTeam <> NULL) OR (@expertTeam > 0)
            SET @mySQL = @mySQL + ' AND ( t.expertTeam = ' + CAST(@expertTeam AS VARCHAR) +' )'
      IF (@startDate <> NULL)  OR (@startDate >= GETDATE())
            SET @mySQL = @mySQL + ' AND ( t.publishDate >= ' + CAST(@startDate AS VARCHAR) +' )'
      IF (@endDate <> NULL)  OR (@endDate >= GETDATE())
            SET @mySQL = @mySQL + ' AND ( t.publishDate <= ' + CAST(@endDate AS VARCHAR) +' )'
      EXECUTE (@mySQL)
      
      -- get all website records
      SET @mySQL = 'INSERT INTO #tempTable SELECT t.[id],t.publishDate,ta.title + ''[website: + t.url + ]'' FROM AS title tblCoachComments WHERE t.url <> NULL'
      IF (@s <> NULL) OR LEN(@s)=0
            SET @mySQL = @mySQL + ' AND ( t.title LIKE ''' + @likeText + ''' OR t.shortDescription LIKE ''' + @likeText + ''' OR t.[description] LIKE ''' + @likeText + ''' )'
      IF (@expertTeam <> NULL) OR (@expertTeam > 0)
            SET @mySQL = @mySQL + ' AND ( t.expertTeam = ' + CAST(@expertTeam AS VARCHAR) +' )'
      IF (@startDate <> NULL)  OR (@startDate >= GETDATE())
            SET @mySQL = @mySQL + ' AND ( t.publishDate >= ' + CAST(@startDate AS VARCHAR) +' )'
      IF (@endDate <> NULL)  OR (@endDate >= GETDATE())
            SET @mySQL = @mySQL + ' AND ( t.publishDate <= ' + CAST(@endDate AS VARCHAR) +' )'
      EXECUTE (@mySQL)
      
      -- get all standalone records
      SET @mySQL = 'INSERT INTO #tempTable SELECT t.[id],t.publishDate,ta.title FROM tblCoachComments WHERE ( (t.article IS NULL) AND (t.tip IS NULL) AND (t.url IS NULL) )'
      IF (@s <> NULL) OR LEN(@s)=0
            SET @mySQL = @mySQL + ' AND ( t.title LIKE ''' + @likeText + ''' OR t.shortDescription LIKE ''' + @likeText + ''' OR t.[description] LIKE ''' + @likeText + '" )'
      IF (@expertTeam <> NULL) OR (@expertTeam > 0)
            SET @mySQL = @mySQL + ' AND ( t.expertTeam = ' + CAST(@expertTeam AS VARCHAR) +' )'
      IF (@startDate <> NULL)  OR (@startDate >= GETDATE())
            SET @mySQL = @mySQL + ' AND ( t.publishDate >= ' + CAST(@startDate AS VARCHAR) +' )'
      IF (@endDate <> NULL)  OR (@endDate >= GETDATE())
            SET @mySQL = @mySQL + ' AND ( t.publishDate <= ' + CAST(@endDate AS VARCHAR) +' )'
      EXECUTE (@mySQL)
      
      --  now return the records ordered by date
      SELECT
            t.[id],
            t.title
      FROM
            #tempTable t
      ORDER BY
            t.publishDate DESC
      
      -- turn on back on rows affected
      SET NOCOUNT OFF

      -- drop table
      DROP TABLE #tempTable

GO
0

Featured Post

Technology Partners: 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!

Question has a verified solution.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

877 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