dynamic query

hi,
how far dynamic query built inside stored procedures is efficient than
normal queries inside stored procedures.
does dynamic query have advantage over ad hoc queries or not and in
what way.
kalai vananHi
http://www.sommarskog.se/dynamic_sql.html
"kalaivanan" <mail2kalai@.gmail.com> wrote in message
news:1164110912.592826.4610@.m73g2000cwd.googlegroups.com...
> hi,
> how far dynamic query built inside stored procedures is efficient than
> normal queries inside stored procedures.
> does dynamic query have advantage over ad hoc queries or not and in
> what way.
> kalai vanan
>

dynamic query

hi,
how far dynamic query built inside stored procedures is efficient than
normal queries inside stored procedures.
does dynamic query have advantage over ad hoc queries or not and in
what way.
kalai vanan
Hi
http://www.sommarskog.se/dynamic_sql.html
"kalaivanan" <mail2kalai@.gmail.com> wrote in message
news:1164110912.592826.4610@.m73g2000cwd.googlegrou ps.com...
> hi,
> how far dynamic query built inside stored procedures is efficient than
> normal queries inside stored procedures.
> does dynamic query have advantage over ad hoc queries or not and in
> what way.
> kalai vanan
>
sql

dynamic query

hi,
how far dynamic query built inside stored procedures is efficient than
normal queries inside stored procedures.
does dynamic query have advantage over ad hoc queries and in what way.

kalai vanankalaivanan wrote:

Quote:

Originally Posted by

how far dynamic query built inside stored procedures is efficient than
normal queries inside stored procedures.

The issue is caching query plans.

Quote:

Originally Posted by

does dynamic query have advantage over ad hoc queries and in what way.

They're more powerful - if you need it, and if you can afford the
disadvantages.

See http://www.sommarskog.se/dynamic_sql.html for more info.|||kalaivanan wrote:

Quote:

Originally Posted by

hi,
how far dynamic query built inside stored procedures is efficient than
normal queries inside stored procedures.
does dynamic query have advantage over ad hoc queries and in what way.
>
kalai vanan

If you pass object name as parameters, you have no choice other than
using dynamic sql
Make sure you read the suggested article fully

Madhivanan

dynamic query

hi,
how far dynamic query built inside stored procedures is efficient than
normal queries inside stored procedures.
does dynamic query have advantage over ad hoc queries or not and in
what way.
kalai vananHi
http://www.sommarskog.se/dynamic_sql.html
"kalaivanan" <mail2kalai@.gmail.com> wrote in message
news:1164110912.592826.4610@.m73g2000cwd.googlegroups.com...
> hi,
> how far dynamic query built inside stored procedures is efficient than
> normal queries inside stored procedures.
> does dynamic query have advantage over ad hoc queries or not and in
> what way.
> kalai vanan
>

Dynamic query

I use Dynamic Query:
="Select dbo.UserList.UserID FROM dbo.UserList
WHERE dbo.UserList.IncidentID = " & Parameters!IncidentID.Value
While I built the solution I had:
The expression for the query ?DataSource2? contains an error: [BC30648]
String constants must end with a double quote.
Tried to play with quotes - nothing helps. What can be wrong?
Thank you in advance.I assume that IncidentID is a varchar or something like that. So you need to
enclose your parameter is single quotes. When figuring this out I usually
have a textbox that I set the expression to so I can see the result. What
you want is this:
="Select dbo.UserList.UserID FROM dbo.UserList WHERE dbo.UserList.IncidentID
= '" & Parameters!IncidentID.Value & "'"
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Irina Pippin via SQLMonster.com" <forum@.nospam.SQLMonster.com> wrote in
message news:80756350b0964be882d691763129e7bd@.SQLMonster.com...
>I use Dynamic Query:
> ="Select dbo.UserList.UserID FROM dbo.UserList
> WHERE dbo.UserList.IncidentID = " & Parameters!IncidentID.Value
> While I built the solution I had:
> The expression for the query 'DataSource2' contains an error: [BC30648]
> String constants must end with a double quote.
> Tried to play with quotes - nothing helps. What can be wrong?
> Thank you in advance.|||another solution
=string.Format("Select dbo.UserList.UserID FROM dbo.UserList WHERE
dbo.UserList.IncidentID ='{0}' ", Parameters!IncidentID.Value)
"Irina Pippin via SQLMonster.com" <forum@.nospam.SQLMonster.com> wrote in
message news:80756350b0964be882d691763129e7bd@.SQLMonster.com...
>I use Dynamic Query:
> ="Select dbo.UserList.UserID FROM dbo.UserList
> WHERE dbo.UserList.IncidentID = " & Parameters!IncidentID.Value
> While I built the solution I had:
> The expression for the query ?DataSource2? contains an error: [BC30648]
> String constants must end with a double quote.
> Tried to play with quotes - nothing helps. What can be wrong?
> Thank you in advance.|||Cool, I'd never seen that solution before. I like it, especially if you have
multiple parameters.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Oleg Yevteyev" <myfirstname001atgmaildotcom> wrote in message
news:eRVfDfpUFHA.548@.tk2msftngp13.phx.gbl...
> another solution
> =string.Format("Select dbo.UserList.UserID FROM dbo.UserList WHERE
> dbo.UserList.IncidentID ='{0}' ", Parameters!IncidentID.Value)
> "Irina Pippin via SQLMonster.com" <forum@.nospam.SQLMonster.com> wrote in
> message news:80756350b0964be882d691763129e7bd@.SQLMonster.com...
>>I use Dynamic Query:
>> ="Select dbo.UserList.UserID FROM dbo.UserList
>> WHERE dbo.UserList.IncidentID = " & Parameters!IncidentID.Value
>> While I built the solution I had:
>> The expression for the query 'DataSource2' contains an error: [BC30648]
>> String constants must end with a double quote.
>> Tried to play with quotes - nothing helps. What can be wrong?
>> Thank you in advance.
>

dynamic killer

Hi, guys!

I want to build a scheduled job to kill any connections from server
'WS1187' built by some VB applications. The code reads something like
this:

declare @.id int
begin
set @.id = (select spid from sysprocesses where hostname='WS1187' and
program_name='Visual Basic')
kill @.id
end
GO

It doesn't compile. The error message is
"Server: Msg 170, Level 15, State 1, Line 5
Line 4: Incorrect syntax near '@.id'."

Is there any way we can kill a user process with a dynamic "spid"
built based on certain business rules?

Thanks in advance.

Gary"Gary" <rooty_hill2002@.yahoo.com.au> wrote in message
news:171bd226.0410072058.4d1b0ab0@.posting.google.c om...
> Hi, guys!
> I want to build a scheduled job to kill any connections from server
> 'WS1187' built by some VB applications. The code reads something like
> this:
> declare @.id int
> begin
> set @.id = (select spid from sysprocesses where hostname='WS1187' and
> program_name='Visual Basic')
> kill @.id
> end
> GO
> It doesn't compile. The error message is
> "Server: Msg 170, Level 15, State 1, Line 5
> Line 4: Incorrect syntax near '@.id'."
> Is there any way we can kill a user process with a dynamic "spid"
> built based on certain business rules?
> Thanks in advance.
> Gary

Check the syntax for KILL in BOL, and you'll see that it doesn't allow a
variable for the SPID, so you need to use dynamic SQL:

exec('kill ' + convert(varchar, @.id))

Simon