Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2003-11-12
7
Medium Priority
?
589 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
7 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

715 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