Solved

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

Posted on 2003-11-12
7
585 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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
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…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

624 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