• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 600
  • Last Modified:

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

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
smacca
Asked:
smacca
  • 3
  • 2
1 Solution
 
RimvisCommented:
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
 
smaccaAuthor Commented:
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
 
smaccaAuthor Commented:
ANSWER: The execute statement must be contained in brackets otherwise string is not encapsulated.

EXECUTE (@mySQL)
0
 
RimvisCommented:
>> 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
 
smaccaAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now