Creating simple dynamic sql

  • I am learning dynamic SQL and I am trying to get information from table using a dynamic WHERE clause.

    For example, we have 2 queries

    a. SELECT * FROM Movies WHERE MovieID = '1234'

    b. SELECT * FROM Movies WHERE MovieName = 'ABCD'

    Since both queries are similar. Only difference is column name in WHERE clause and value. So I am try to create a dynamic sql query that can take two variables (cloumn name and value) and do the job.

    For that I tried to create following stored procedure, it does executes properly but does not give me correct result.

    CREATE PROCEDURE [dbo].[AdminSelectMovie]

    @Movies VARCHAR(20), --For where clause. It can be MovieID or Title column

    @SearchString VARCHAR(35) --Search string for value

    AS

    BEGIN

    SET NOCOUNT ON;

    DECLARE @sql nvarchar(500)

    SET @sql = 'SELECT MovieID, Title, Descript, Duration FROM Movies WHERE ' + @Movies + ''

    SET @sql = @sql + ' = '+@SearchString+''

    EXEC sp_executesql @sql

    END

    When ever I try to execute this query from asp it gives me following error

    Microsoft OLE DB Provider for SQL Server error '80040e14'

    Invalid column name

    I am not sure where I am wrong.

    Can any body help me.

    Thanks in advance.

  • What are you passing in for @movies?

  • column name

    that can be Title or MovieID

  • OK, wanted to be sure.

    I think the problem is that you don't have your string enclosed in quotes. If you were to print the dynamic SQL, I bet it would look something like:

    SELECT MovieID, Title, Descript, Duration FROM Movies

    WHERE MovieID = 1234

    instead of

    SELECT MovieID, Title, Descript, Duration FROM Movies

    WHERE MovieID = '1234'

    Your dynamic SQL needs to have quotes around the string.

  • You don't need dynamic sql for this...

    a. SELECT * FROM Movies WHERE MovieID = @MovieID

    b. SELECT * FROM Movies WHERE MovieName = @MovieName

    The only time you need really need dynamic SQL is in the FROM clause or if you need to do something totally exotic in things like GROUP BY or the SELECT list.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I added quote in the query as below. But now it gives following error

    -----------------------------------------------------

    ADODB.Field error '80020009'

    Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

    ----------------------------------------

    I am doing self study on this and I am pretty sure I am missing some thing important, but I am not sure what it is. Can some body please help.

    ----------------------------------------

    ALTER PROCEDURE [dbo].[AdminSelectMovie]

    @Movies VARCHAR(20), --For where clause. It can be MovieID or Title column

    @SearchString VARCHAR(35)

    AS

    BEGIN

    SET NOCOUNT ON;

    DECLARE @sql nvarchar(500)

    SET @sql = 'SELECT MovieID, Title, Descript, Duration FROM Movies WHERE ' + @Movies + ''

    SET @sql = @sql + ' = ''+@SearchString+'''

    EXEC sp_executesql @sql

    END

  • I know it can be done using simple stored procedure using IF condition but I am just trying to play with dynamic sql and I was trying to implement this in my hobby website so please any solutions.

    Thanks

  • Thanks every body I solved my problem. Here is the solution.

    I am really grateful to every body for help and time

    ALTER PROCEDURE [dbo].[AdminSelectMovie]

    @Movies VARCHAR(20), --For where clause. It can be MovieID or Title column

    @SearchString VARCHAR(35)

    AS

    BEGIN

    --SET NOCOUNT ON;

    DECLARE @sql nvarchar(500)

    SET @sql = 'SELECT * FROM Movies WHERE '+@Movies+' = '''+@SearchString+''''

    EXEC sp_executesql @sql

    END

  • I often get confused on how to use quotes properly in dynamic sql.

    I don't use it very often, but I end up doing trial and error until it works.

    Is there any articles or anything else that can explain the rules or proper use of quotes in dynamic sql?

    "There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

  • I know you're trying to learn dynamic sql, but I would take a simpler approach simply so another person could easily understand.

    ALTER PROCEDURE [dbo].[AdminSelectMovie]

    @MovieID int = null,

    @Title varchar(35) = null

    AS

    BEGIN

    SELECT * FROM Movies WHERE

    (@MovieID is null or [MovieID]=@MovieID)

    AND

    (@Title is null or [Title]=@Title)

    END

  • You have to escape the quote, so double it up.

    Meaning that a single quote needs 2. If it is adjacent to an opening or closing quote, you'll have 3

    select '''test'''

    The best way to do this is not execute the dynamic SQL, but build it and return it first with a SELECT. Then once you're sure it's OK, you can change the SELECT to an EXEC().

    Note, that if you have users submitting strings, you need to make sure you do a REPLACE to double up their quotes. However this is where SQL Injection becomes a problem. If a user can submit a string into dynamic SQL, they can add in a ";shutdown" or other command to their string, which would be executed.

    In general, you should avoid dynamic SQL. In this case, I'd write two procedures, or two calls so that there was no need for dynamic SQL, and I had clear procedures dedicated to something. The extra query isn't much of a maintenance item and you are adding the potential for a security hole.

  • lonesome (11/23/2009)


    Thanks every body I solved my problem. Here is the solution.

    I am really grateful to every body for help and time

    ALTER PROCEDURE [dbo].[AdminSelectMovie]

    @Movies VARCHAR(20), --For where clause. It can be MovieID or Title column

    @SearchString VARCHAR(35)

    AS

    BEGIN

    --SET NOCOUNT ON;

    DECLARE @sql nvarchar(500)

    SET @sql = 'SELECT * FROM Movies WHERE '+@Movies+' = '''+@SearchString+''''

    EXEC sp_executesql @sql

    END

    I agree with what Dustin posted... I know you're trying to learn dynamic SQL but there's absolutely no need for it here. Please see his post above and take a look at the code.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I am not saying whether you should or shouldn't use dynamic SQL but since you are trying to learn it, here is a way you might write something to play around with. Dynamic sql is definitely useful but must be done in a proper manner. The only caveate is I don't really know how your table is set up so I just went with the guesses other had before me in this post. (And I assume SQL Server 2005 at least.) Hope it helps you think of ways that you may or may not find dynamic sql useful.

    IF OBJECT_ID(N'[dbo].[AdminSelectMovie]') IS NULL

    BEGIN

    EXEC ('CREATE PROCEDURE

    [dbo].[AdminSelectMovie]

    AS BEGIN SELECT ''STUB'' END');

    END;

    GO

    SET ANSI_NULLS ON;

    GO

    SET QUOTED_IDENTIFIER ON;

    GO

    ALTER PROCEDURE [dbo].[AdminSelectMovie]

    @MovieID int = NULL

    ,@MovieName varchar(100) = NULL

    AS

    BEGIN TRY

    --just picked these field sizes to be careful, adjust to proper size depending on

    --the size of your final sql string's largest possible size

    DECLARE @sqlSELECT nvarchar(400);

    DECLARE @sqlWHERE nvarchar(800);

    DECLARE @sqlALL nvarchar(max);

    DECLARE @parmDefinition nvarchar(100);

    --create SELECT which usually stay same

    SET @sqlSELECT = N' SELECT '

    + N' MovieID'

    + N' ,Title'

    + N' ,Descript'

    + N' ,Duration'

    + N' FROM Movies M' ;

    --Now do the WHERE which is usually the part that needs the dynamic

    SET @sqlWHERE = N' WHERE 1 = 1'; --this allows us to run query whether the rest are added or not

    IF @MovieID IS NOT NULL BEGIN

    SET @sqlWHERE = @sqlWHERE + N' AND M.MovieID = @dynMovieID';

    END;

    IF @MovieName IS NOT NULL BEGIN

    SET @sqlWHERE = @sqlWHERE + N' AND M.MovieName = @dynMovieName';

    END;

    --put all possible params in here, whether or not they actually get used

    --in the dynamically created SELECT they will need to be there for the

    --below EXEC sp_executesql

    SET @parmDefinition = N'@dynMovieID int, @dynMovieName varchar(100)';

    --create the full select string

    SET @sqlALL = @sqlSELECT + @sqlWHERE;

    EXEC sp_executesql

    @sqlALL

    ,@parmDefinition

    ,@dynMovieID=@MovieID

    ,@dynMovieName=@MovieName;

    END TRY

    BEGIN CATCH

    --Do actual needed stuff here to recover from error

    --In the mean time just give me the errors

    SELECT ERROR_NUMBER() [ErrorNumber]

    ,ERROR_SEVERITY() [ErrorSeverity]

    ,ERROR_STATE() [ErrorState]

    ,ERROR_PROCEDURE() [ErrorProcedure]

    ,ERROR_LINE() [ErrorLine]

    ,ERROR_MESSAGE() [ErrorMessage]

    ;

    END CATCH;

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply