insert the value to the temp table
-
I tried to insert the value from the select query with a few optional parameter to temp table but I do not know how to accompish this.
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/*. according the due date end user select and ALTER temp table
find the nomatch table in the occh and return the table
*/
ALTER PROCEDURE dbo.test
@quiz VARCHAR( 25 ),
@fromdate datetime = NULL,
@todate datetime = NULL,
@debug bit = 0
as
DECLARE @sql nvarchar(4000),
@paramlist nvarchar(4000)
begin
CREATE TABLE #tmpTrained(
EmpID char(30))
set @sql='select distinct EmplNO from tblCurrentWinTrainingLog where 1=1'
set @sql= @sql+ ' AND L.quizname = '+ @quiz
IF @fromdate IS NOT NULL
set @sql = @sql + ' AND L.CTDate >='+@fromdate
IF @todate IS NOT NULL
set @sql = @sql + ' AND L.CTDate <='+ @todate
IF @Debug = 1
BEGIN
PRINT @sql PRINT ''
END
set @sql='Insert into #tempTrained EmpID value '+ @sql
exec @sql
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
GO
I gave me error
select distinct EmplNO from tblCurrentWinTrainingLog where 1=1 AND L.quizname = Driver
Server: Msg 2812, Level 16, State 62, Line 46
Could not find stored procedure 'Insert into #tempTrained EmpID value select distinct EmplNO from tblCurrentWinTrainingLog where 1=1 AND L.quizname = Driver'.
Stored Procedure: InternalEdu.dbo.test
Return Code = 0
I looked at this link for sample, it made me more confused. Thx.
-
I see a couple of things just by looking at it, but the most important thing here is that you do not need dynamic SQL to populate your temp table. Use a static SQL statement instead such as :
INSERT INTO #tmpTable
SELECT Col1,
Col2,
Etc
FROM TableName
Another thing that I see is that your variables are out of whack. I assume this is just an example so I won't point out everything that is wrong. The main point, though, is that you do not need to use Dynamic SQL for this example. In addition, depending on your use of the temp table, you probably don't need that either. It would be more helpful, and beneficial to you, to post your real stored procedure instead of a mock up example.
-
Use Dynamic Or Static
Please make sure you use syntax
EXEC (@SQL) instead if EXEC @SQL
Ram
-
but right now it is error.
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/*. according the due date end user select and ALTER temp table
find the nomatch table in the occh and return the table
*/
ALTER PROCEDURE dbo.test
@quiz VARCHAR( 25 ),
@fromdate datetime = NULL,
@todate datetime = NULL,
@debug bit = 0
as
DECLARE @sql nvarchar(4000),
@paramlist nvarchar(4000)
begin
CREATE TABLE #tmpTrained(
EmplID char(30))
set @sql='select distinct EmplNO from tblCurrentWinTrainingLog where 1=1'
set @sql= @sql+ ' AND quizname = '+ @quiz
IF @fromdate IS NOT NULL
set @sql = @sql + ' AND CTDate >='+@fromdate
IF @todate IS NOT NULL
set @sql = @sql + ' AND CTDate <='+ @todate
select @sql='+ @sql +'
--date conversion error here.
set @sql='Insert into #tmpTrained '+ @sql
IF @Debug = 1
BEGIN
PRINT @sql PRINT ''
END
exec (@sql)
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-
Again, this does not need to be dynamic sql. You say that you are getting an error, what is the error?
-
convert the data to string.
date time conversion error.
convert(datetime,CTDate)
this line
IF @fromdate IS NOT NULL
set @sql = @sql + ' AND convert(datetime, CTDate) >='+@fromdate
IF @todate IS NOT NULL
set @sql = @sql + ' AND convert(datetime,CTDate) =<'+ @todate
select @sql='+ @sql +'
-
try this
SET @Sql = @Sql + 'AND CTDate <= CAST('''+@todate+''' AS DATETIME))'
-
Since you are concatenating your variables @fromdate and @todate to a string, you need to convert them to a string as well.
And for the third time, you do not need dynamic sql to accomplish what you are doing. If you are not aware of the downside to dynamic sql, read this (for starters): http://www.sommarskog.se/dynamic_sql.html
If you can a description of exactly what you are trying to accomplish with your stored procedure, we will be better able to assist you in creating a working SET based solution for you using static SQL. Yea, yea, I know that you are trying to populate a temp table, but why? What is the purpose of the data that you are putting into the temp table?
-
I read the link you mentioned about, too.
It is hard to implement. Here what I want to accomplish ?
http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=23&messageid=315364
Thanks.
-
By the way, Sriram, your suggestion will not work either. Did you try it? It gives syntax errors. If you truely want to go the Dynamic route (again, not suggested), here's the correct syntax:
set @sql = @sql + 'AND CTDate <= ' + CAST(@todate AS varchar)
I will look at your other post and get back to you.
-
Looks like you also need to put @quiz in quotes
set @sql= @sql+ ' AND quizname = '''+ @quiz + ''''
-
-
Jeff Moden asks a relevent question in his post. If you answer his questions, you'll get the solution you're after without having to continue this post. I will look for a response in your other post.
-
Thx. Today I need to take my son to visit school. I will not have time to test. I will test and get back tomorrow. Thx for big help.
-
I tried this
SET @Sql = @Sql + 'AND CTDate <= CAST('''+@todate+''' AS DATETIME))' .
It will give me error as
Server: Msg 241, Level 16, State 1, Procedure test, Line 34
Syntax error converting datetime from character string..
I think because @todate @formdate can't not use + .
select distinct EmplNO from tblCurrentWinTrainingLog where 1=1 AND quizname = 'Driver Safety' AND CTDate >= Jan 5 2006 12:00AM AND CTDate =< Aug 5 2007 12:00AM
set @sql = @sql + 'AND CTDate <= ' + CAST(@todate AS varchar)
if I entered the date 01/05/06 and 08/05/07.
it cast the date as Jan 5 2006 12:00 am ,etc
select distinct EmplNO from tblCurrentWinTrainingLog where 1=1 AND quizname = 'Driver Safety' AND CTDate >= Jan 5 2006 12:00AM AND CTDate =< Aug 5 2007 12:00AM.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '5'.
if I use this
convert(varchar, @fromdate,103) or convert(varchar, @fromdate,101)
select distinct EmplNO from tblCurrentWinTrainingLog where 1=1 AND quizname = 'Driver Safety' AND CTDate >=07/08/2006 AND CTDate =<05/10/2007
it gave me error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '<'.
Stored Procedure: InternalEdu.dbo.test
Return Code = 0
Thx.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply