November 16, 2007 at 3:03 am
Hello,
I have a problem to create a variable. The variable must be enclosed by single quotes. In the VARCHAR variable must also be another variable (NUMERIC).
DECLARE @sql VARCHAR(8000)
DECLARE @p_number NUMERIC
SET @p_number = 134070
SET @sql = ????
The result of SELECT @sql must be:
'SELECT * FROM M_Driemnd(134070)'
where 134070 of course must be the @p_number variable. This all must be inside of a cursor, each time the cursor runs I change @p_number
Can anyone provide me with the correct syntax of the SET @sql= ??
I have been busy for several days now and feel that I get stuck in my own wrong way in the solution. I need a fresh thought to get me started in a different way.
Thanks in advance.
Pim Verkley
November 16, 2007 at 3:07 am
select @sql='''SELECT * FROM M_Driemnd('+convert(varchar(255),@p_number)+')'''
November 16, 2007 at 3:13 am
Try the following way:
DECLARE @sql VARCHAR(8000)
DECLARE @p_number NUMERIC
SET @p_number = 134070
SET @sql = 'SELECT * FROM M_Driemnd(' + CONVERT (varchar (10), @p_number) + ')'
PRINT @sql
🙂
November 16, 2007 at 3:21 am
In reply to EvilPostIT:
Great this works!!!
In reply to Kishore.P I can say, this works, only the single quotes to enclose the string are missing.
I added some quotes and now it works:
SET @sql = '''SELECT * FROM M_Driemnd(' + CONVERT (varchar (10), @p_number) + ')'''
November 16, 2007 at 4:55 am
You've got the solution.
Just a reminder there are some downsides for using dynamic sql
All time ref: www.sommarskog.se/dynamic_sql.html :Whistling:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 19, 2007 at 10:42 am
You can also use char(39) as a single quote.
Example.
Select char(39) + name + char(39) from sysobjects
Just build your string using char(39) to surround your strings. I find it alot easier than adding a bunch of single quotes. '''''
Tom
November 19, 2007 at 12:19 pm
Tom Goltl (11/19/2007)
You can also use char(39) as a single quote.Example.
Select char(39) + name + char(39) from sysobjects
Just build your string using char(39) to surround your strings. I find it alot easier than adding a bunch of single quotes. '''''
Tom
Ah, that is something I need to look intop much deeper, thanks for you're thouht.
August 15, 2016 at 6:02 am
This is really helpful tom. Thanks !!!!
Tom Goltl (11/19/2007)
You can also use char(39) as a single quote.Example.
Select char(39) + name + char(39) from sysobjects
Just build your string using char(39) to surround your strings. I find it alot easier than adding a bunch of single quotes. '''''
Tom
August 15, 2016 at 6:54 am
Just because this thread was revived after 9 years, I'd like to make a few suggestions.
Use parametrized dynamic sql and always define precision and scale for your types. In 2000, there was the restriction of 4000 characters, in 2005 the restriction basically disappears with NVARCHAR(MAX).
DECLARE @sql NVARCHAR(MAX);
DECLARE @p_number NUMERIC(18, 0);
SET @p_number = 134070;
SET @sql = 'SELECT * FROM M_Driemnd(@p_number)';
EXEC sp_executesql @sql, N'@p_number NUMERIC(18, 0)', @p_number;
There's also no case on making this code dynamic, but a more complex process might be worth of it. This should only be treated as an example.
September 18, 2016 at 9:12 pm
Tom Goltl (11/19/2007)
You can also use char(39) as a single quote.Example.
Select char(39) + name + char(39) from sysobjects
Just build your string using char(39) to surround your strings. I find it alot easier than adding a bunch of single quotes. '''''
Tom
That's a quite common mistake and a cause of numerous run-time errors in many applications.
If a name has a single quote in it the resulting SQL string will be invalid.
Not to mention exposure to SQL injections.
For names not longer than 128 characters use
Select QUOTENAME(name, char(39)) from sysobjects
For longer names you have to do it like this:
Select char(39) + REPLACE(name, char(39), char(39)+CHAR(39)) + char(39) from sysobjects
_____________
Code for TallyGenerator
September 22, 2016 at 5:35 pm
On the topic of running
'SELECT * FROM M_Driemnd(@p_number)'
inside of a cursor....
Not sure what the original objective was but if I am understanding well enough, I imagine can be readily handled with a tally table.
Something like :
declare @p_numberMax int; /* = some large value that you set. */
SELECTtvf.*
FROM(Select number
FromtallyTable
Wherenumber >= 1 and number <= @p_numberMax
) as t
CROSS APPLY M_Driemnd(t.number) as tvf
No need for dynamic SQL, except for the fact that Cross Apply did not become available until Sql Server 2005.
----------------------------------------------------
September 22, 2016 at 7:19 pm
For older versions of SQL SERVER : A simple (non table valued) function >
if object_id('fcnTEST') is not null
drop function fcnTest;
GO
CREATEFUNCTION dbo.fcnTEST(@param decimal(10,3))
RETURNS decimal(10,3)
AS
begin
return @param *2
end
GO
----------------------------
selectdbo.fcnTEST(t.[n]) as calc
from(
selectn
fromtally /* the numbers table */
wheren >=1 and n <= 100 /* <-- replace with your max value */
)as t
No need for cursors, looping or dynamic SQL
----------------------------------------------------
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply