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

-------------------------------------------
smaccaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.