March 8, 2012 at 5:19 am
Hello, could anybody help.
I would like to know how to properly put variable(@dat1) into SQLString.
input data: @dd1 = 1.3.2012 (dd.mm.yyyy)
example:
ALTER Procedure dbo.VYK
(
@dd1 char(20)
)
As
DECLARE @dat1 as datetime
DECLARE @SQLString NVARCHAR(4000)
SET @dat1 = CONVERT(DATETIME, @dd1, 104)
IF OBJECT_ID('tempdb.dbo.##vykony') IS NOT NULL
DROP TABLE tempdb.dbo.##vykony
SET @SQLString = N'
SELECT id, Datum, idartikel
INTO ##vykony
FROM dbo.Vykony
WHERE Datum > @dat1'
EXEC sp_executesql @SQLString
return
Thanks in advance!
March 8, 2012 at 8:25 am
Two questions.
1) Why global temp tables? You are dropping a global temp table which means if you have another process using it you just dumped it in the middle of that process.
2) Why do you need dynamic sql for this?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 8, 2012 at 9:53 am
Two answers.
1) you can ignore it , it is just an example
2) 'cos I will use it via MS access...
All I want to know is how to write variable into SQLstring ...that is all
March 8, 2012 at 9:59 am
You want the value of the variable so it is just string concatenation.
declare @MyVar varchar(10) = 'like this.'
declare @SQLString varchar(max)
set @SQLString = 'You append your variable ' + @MyVar
select @SQLString
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 8, 2012 at 10:57 am
okay I know , but not what I want ... try to apply it on my example.
again: input parameter is date:e.g. 1.3.2012
DECLARE @dat1 as datetime
DECLARE @SQLString NVARCHAR(4000)
SET @dat1 = CONVERT(DATETIME, @dd1, 104)
SET @SQLString = N'
SELECT id, Datum, idartikel
INTO ##vykony
FROM dbo.Vykony
WHERE Datum > @dat1'
/* I think that variable @dat1 which contains datetime value should be with marks ... but I cant remember if this ' + @variable + ' or something else, because it is datetime variable*/
EXEC sp_executesql @SQLString
March 8, 2012 at 11:06 am
peter478 (3/8/2012)
okay I know , but not what I want ... try to apply it on my example.again: input parameter is date:e.g. 1.3.2012
DECLARE @dat1 as datetime
DECLARE @SQLString NVARCHAR(4000)
SET @dat1 = CONVERT(DATETIME, @dd1, 104)
SET @SQLString = N'
SELECT id, Datum, idartikel
INTO ##vykony
FROM dbo.Vykony
WHERE Datum > @dat1'
/* I think that variable @dat1 which contains datetime value should be with marks ... but I cant remember if this ' + @variable + ' or something else, because it is datetime variable*/
EXEC sp_executesql @SQLString
The problem is that you converted a datetime to a datetime. @dat1 is still a datetime.
Is this what you need?
DECLARE @dat1 as varchar(10)
DECLARE @SQLString NVARCHAR(4000)
SET @dat1 = CONVERT(varchar(10), getdate(), 104)
SET @SQLString = N'
SELECT id, Datum, idartikel
INTO ##vykony
FROM dbo.Vykony
WHERE Datum > ''' + @dat1 + ''''
select @SQLString
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 8, 2012 at 11:27 am
Yes, it was the problem, thank you!
March 8, 2012 at 1:09 pm
you're welcome.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 8, 2012 at 3:12 pm
You should not do any string concating. If you're going to use sp_executesql, then use it properly and specify the date as a parameter.
DECLARE @SQLString NVARCHAR(4000)
SET @SQLString = N'
SELECT id, Datum, idartikel
INTO ##vykony
FROM dbo.Vykony
WHERE Datum > @dat1'
EXEC sp_executesql @SQLString, N'@dat1 datetime', @dd1
March 8, 2012 at 3:20 pm
And also the global temp table is not needed at all. For example:
DECLARE @dd1 datetime = dateadd(year, -1, getdate());
DECLARE @SQLString NVARCHAR(4000);
SET @SQLString = N'
select t.name, t.object_id
from sys.tables t
where t.modify_date > @dat1';
declare @tables table (
name sysname not null,
object_id int not null
);
insert @tables (name, object_id)
EXEC sp_executesql @SQLString, N'@dat1 datetime', @dd1;
select * from @tables;
March 8, 2012 at 11:42 pm
Thank you guys, yes I'm going to use sp_executesql, but in case I had more parameters
(e.g. dd1, dd2..etc) how can I write it then?
EXEC sp_executesql @SQLString, N'@dat1 datetime', @dd1
March 9, 2012 at 12:10 am
The 1st parameter to sp_executesql contains the T-SQL text you need executed. This text may contain any number of variables, in any order and the same parameter may be used multiple times.
In the 2nd parameter you declare the parameters plus their types, Separate each set of name plus type by a comma. Note that both the 1st and the 2nd parameter have to be of type Nvarchar. You specified nvarchar(4000), nvarchar(max) is however very acceptable here too.
Then the 3rd and next parameters are the values for the parameters, in the order you declared them in the 2nd parameter.
For example:
create table dbo.MyTable (
ID int identity(1,1) not null,
Date1 datetime not null,
Value numeric(25, 2) not null,
constraint PK_MyTable primary key (ID)
);
create index ix_MyTable_Date1 on dbo.MyTable( Date1) include (Value);
declare @dd1 datetime = {d '2010-01-01'};
declare @dd2 datetime = {d '2010-02-01'};
declare @n int = 100;
declare @SQLString nvarchar(max) = N'
select top (@maxrows) t.Date1, t.Value, @dat1
from dbo.MyTable t
where t.Date1 >= @dat1
and t.Date1 < @dat2
order by t.Date1';
declare @nReturn int;
declare @Results table (
Date1 datetime not null,
Value numeric(25, 2) not null,
StartDate datetime null
);
insert @Results (Date1, Value, StartDate)
EXEC @nReturn = sp_executesql @SQLString,
N'@dat1 datetime, @dat2 datetime, @maxrows int',
@dd1,
@dd2,
@n;
if @nReturn = 0
select * from @results;
else
raiserror ( 'Could not execute search.', 16, 1);
You can, depending on the values specified for @dd1, @dd2 and @n, choose to change the T-SQL. For example if @dd1 is passed in as null, you could dynamically build the following statement:
select top (@maxrows) t.Date1, t.Value, @dat1
from dbo.MyTable t
where t.Date1 < @dat2
order by t.Date1
instead of the T-SQL presented above. This will make sure that your search always uses an optimal query plan for the search parameters specified. Definitly read Gail's article on catch-all-queries if you intend to follow such a path: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
March 9, 2012 at 3:17 am
okay, thank you for your advice and time.
... however i have one more question 🙂
When is an advantage to use global temp table, because the result is in fact the same, or not?
March 9, 2012 at 5:18 am
The advantage of a global temp table is that you can read/write it from another process. The disadvantage is that you can only have one global temp table with the same name, if 2 users/processes try to create the same table an error will occur for the 2nd. And if not an error occurs, then you may still be reading/writing another processes data. Which is actually even more painfull, as it may prove very hard to debug.
In short, if ever you can avoid using a global temp table: don't use it. I've been programming in T-SQL for over 10 years now and I have not ever had to use one...
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply