November 23, 2009 at 4:37 pm
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.
November 23, 2009 at 4:50 pm
What are you passing in for @movies?
November 23, 2009 at 5:42 pm
column name
that can be Title or MovieID
November 23, 2009 at 9:45 pm
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.
November 23, 2009 at 10:22 pm
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
Change is inevitable... Change for the better is not.
November 23, 2009 at 10:29 pm
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
November 23, 2009 at 10:36 pm
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
November 23, 2009 at 11:46 pm
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
November 25, 2009 at 7:11 am
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
November 25, 2009 at 7:48 am
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
November 25, 2009 at 9:49 am
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.
November 25, 2009 at 12:06 pm
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
Change is inevitable... Change for the better is not.
November 25, 2009 at 1:05 pm
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