April 2, 2012 at 1:26 pm
I am working on a sql server 2008 r2 database to run adhoc queries. I would like to be able to run some sql that uses parameters.
I do not want to setup a stored procedure. All I can find on the internet are ways of how to setup parameters for a stored procedure.
Can you tell me or point me to a reference thagt will show me how to setup some sql that I can execute that takes in parameters?
basically i want to run some sql without hard coding the dates all the time.
April 2, 2012 at 1:29 pm
Something like this?
declare @myDate date
set @myDate = '2012-04-02'
select @myDate as myDate
Obviously this is really simple, but you can use the @myDate in calculations or comparisions like you would any other value.
April 2, 2012 at 1:31 pm
roryp 96873 (4/2/2012)
Something like this?
declare @myDate date
set @myDate = '2012-04-02'
select @myDate as myDate
Obviously this is really simple, but you can use the @myDate in calculations or comparisions like you would any other value.
also works in the where. however you cant use it as table or column names unless you build dynamic queries.
DECLARE @VarBlah
SET @VarBlah = 'SomeOtherStuff'
SELECT Blah from TableBlah where blah = @VarBlah
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 2, 2012 at 1:34 pm
capn.hector (4/2/2012)
roryp 96873 (4/2/2012)
Something like this?
declare @myDate date
set @myDate = '2012-04-02'
select @myDate as myDate
Obviously this is really simple, but you can use the @myDate in calculations or comparisions like you would any other value.
also works in the where. however you cant use it as table or column names unless you build dynamic queries.
DECLARE @VarBlahSET @VarBlah = 'SomeOtherStuff'
SELECT Blah from TableBlah where blah = @VarBlah
My code works, although I see it is probably a little confusing that I aliased my column as the same thing as my variable, minus the @ sign. What I have just returns one value.
April 2, 2012 at 1:37 pm
roryp 96873 (4/2/2012)
capn.hector (4/2/2012)
roryp 96873 (4/2/2012)
Something like this?
declare @myDate date
set @myDate = '2012-04-02'
select @myDate as myDate
Obviously this is really simple, but you can use the @myDate in calculations or comparisions like you would any other value.
also works in the where. however you cant use it as table or column names unless you build dynamic queries.
DECLARE @VarBlahSET @VarBlah = 'SomeOtherStuff'
SELECT Blah from TableBlah where blah = @VarBlah
My code works, although I see it is probably a little confusing that I aliased my column as the same thing as my variable, minus the @ sign. What I have just returns one value.
when i said column or table names i meant the following:
DECLARE @Table VARCHAR(MAX) = 'sometablename'
SELECT * from @Table
or similar but for column names
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 2, 2012 at 1:39 pm
capn.hector (4/2/2012)
roryp 96873 (4/2/2012)
capn.hector (4/2/2012)
roryp 96873 (4/2/2012)
Something like this?
declare @myDate date
set @myDate = '2012-04-02'
select @myDate as myDate
Obviously this is really simple, but you can use the @myDate in calculations or comparisions like you would any other value.
also works in the where. however you cant use it as table or column names unless you build dynamic queries.
DECLARE @VarBlahSET @VarBlah = 'SomeOtherStuff'
SELECT Blah from TableBlah where blah = @VarBlah
My code works, although I see it is probably a little confusing that I aliased my column as the same thing as my variable, minus the @ sign. What I have just returns one value.
when i said column or table names i meant the following:
DECLARE @Table VARCHAR(MAX) = 'sometablename'SELECT * from @Table
or similar but for column names
Ah yes, I get what you're saying. You'll have to pardon me for being slow at times. 😛
April 2, 2012 at 1:42 pm
roryp 96873 (4/2/2012)
capn.hector (4/2/2012)
roryp 96873 (4/2/2012)
capn.hector (4/2/2012)
roryp 96873 (4/2/2012)
Something like this?
declare @myDate date
set @myDate = '2012-04-02'
select @myDate as myDate
Obviously this is really simple, but you can use the @myDate in calculations or comparisions like you would any other value.
also works in the where. however you cant use it as table or column names unless you build dynamic queries.
DECLARE @VarBlahSET @VarBlah = 'SomeOtherStuff'
SELECT Blah from TableBlah where blah = @VarBlah
My code works, although I see it is probably a little confusing that I aliased my column as the same thing as my variable, minus the @ sign. What I have just returns one value.
when i said column or table names i meant the following:
DECLARE @Table VARCHAR(MAX) = 'sometablename'SELECT * from @Table
or similar but for column names
Ah yes, I get what you're saying. You'll have to pardon me for being slow at times. 😛
no problem, as soon as you posted i realized i needed some further clarification.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 2, 2012 at 3:28 pm
By the way, all these examples are for local variables, not parameters. There is a distinct difference. Unless the statement is recompiled, the values within variables will not be "sniffed" (sampled) by the query optimizer. That means any query using a local variable (unless it's recompiled) will only use generic values from the statistics instead of specific values. This is known as parameter sniffing and it's something that enhances your parameterized queries.
If you really want to use a parameter, look at sp_executesql if you have to use dynamic TSQL. This can use true parameters as opposed to local variables.
Just curious, what's wrong with stored procedures?
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 3, 2012 at 8:41 am
My company has such extreme change control procedures, it is difficult to put anything into production. There is so much you need to fill out to put anything like a stored procedure into production. That is why I was looking for an alternative method.
April 3, 2012 at 8:50 am
dianerstein 8713 (4/3/2012)
My company has such extreme change control procedures, it is difficult to put anything into production. There is so much you need to fill out to put anything like a stored procedure into production. That is why I was looking for an alternative method.
In theory, that's to protect against things like SQL injection and to allow for code review, financial testing... All stuff that is actually necessary either from a good coding practices stand point or, in some cases, regulatory requirements. Trying to bypass this might be (might) problematic. I'd exercise caution with that approach.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply