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 Order By!

Hi all, I am new to store procedures and am trying to write one with a
dynamic or..
Below is my feeble attempt if anyone can point me in the right direction
that would be great.
CREATE PROCEDURE get_event_list
@.order_column INT = null
@.order_mode = varchar(4)
AS
BEGIN
SELECT event_code,event_title,event_topic,event_category, event_id FROM
event
ORDER BY
//select a table column based on a numeric value in @.order_column
parameter
//Then determine orientation by value in @.order_mode
//If order_mode = "asc" then the query is to be order ASC and vica versa.
END
AJ
CREATE PROCEDURE get_event_list
(@.order_column INT = NULL,
@.order_mode VARCHAR(4))
AS
BEGIN
SELECT event_code, event_title, event_topic, event_category, event_id
FROM Event
ORDER BY
CASE WHEN @.order_mode='ASC'
AND @.order_column=1 THEN event_code END ASC,
CASE WHEN @.order_mode='ASC'
AND @.order_column=2 THEN event_title END ASC,
...
CASE WHEN @.order_mode='DESC'
AND @.order_column=1 THEN event_code END DESC,
CASE WHEN @.order_mode='DESC'
AND @.order_column=2 THEN event_title END DESC,
...
END
David Portas
SQL Server MVP
|||What I also should say is that you'll probably find you will get better
performance by including all the possible SELECTs in your SP, enclosing each
in an IF statement. If you prefer a single query solution then you can use
the CASE method I have given, or you could try using Dynamic SQL.
David Portas
SQL Server MVP
|||Anthony Judd wrote:
> Hi all, I am new to store procedures and am trying to write one with a
> dynamic or..
> Below is my feeble attempt if anyone can point me in the right direction
> that would be great.
Here is some example I have. I know it is not a fancy solution, but it
is flexible...
CREATE PROCEDURE SomeList
@.akt CHAR(1) = '1',
@.OrderBy VARCHAR(50) = 'NazivUcionice',
@.OrderSort VARCHAR(10) = 'ASC'
AS
DECLARE @.SQLstmt varchar (1000)
SELECT @.SQLstmt =
'SELECT U.ID AS ElementID,
U.Naziv
U.Vrsta,
U.Tip,
U.Vlasnistvo,
U.Aktivna,
U.Broj_mjesta
FROM U
WHERE U.Aktivna=' +@.akt + '
ORDER BY ' + @.OrderBy + ' ' + @.OrderSort
EXEC (@.SQLstmt)
GO
Tomislav Bilic
ICQ: 1824223
http://www.escapestudio.net
sql

Dynamic Order By!

Hi all, I am new to store procedures and am trying to write one with a
dynamic or..
Below is my feeble attempt if anyone can point me in the right direction
that would be great.
CREATE PROCEDURE get_event_list
@.order_column INT = null
@.order_mode = varchar(4)
AS
BEGIN
SELECT event_code,event_title,event_topic,event_category,event_id FROM
event
ORDER BY
//select a table column based on a numeric value in @.order_column
parameter
//Then determine orientation by value in @.order_mode
//If order_mode = "asc" then the query is to be order ASC and vica versa.
END
AJCREATE PROCEDURE get_event_list
(@.order_column INT = NULL,
@.order_mode VARCHAR(4))
AS
BEGIN
SELECT event_code, event_title, event_topic, event_category, event_id
FROM Event
ORDER BY
CASE WHEN @.order_mode='ASC'
AND @.order_column=1 THEN event_code END ASC,
CASE WHEN @.order_mode='ASC'
AND @.order_column=2 THEN event_title END ASC,
...
CASE WHEN @.order_mode='DESC'
AND @.order_column=1 THEN event_code END DESC,
CASE WHEN @.order_mode='DESC'
AND @.order_column=2 THEN event_title END DESC,
...
END
--
David Portas
SQL Server MVP
--|||What I also should say is that you'll probably find you will get better
performance by including all the possible SELECTs in your SP, enclosing each
in an IF statement. If you prefer a single query solution then you can use
the CASE method I have given, or you could try using Dynamic SQL.
--
David Portas
SQL Server MVP
--|||Hey guys,
How do I create views in MSSQL where the source is coming from an Oracle
table?
Thanks.
neil|||Anthony Judd wrote:
> Hi all, I am new to store procedures and am trying to write one with a
> dynamic or..
> Below is my feeble attempt if anyone can point me in the right direction
> that would be great.
Here is some example I have. I know it is not a fancy solution, but it
is flexible...
CREATE PROCEDURE SomeList
@.akt CHAR(1) = '1',
@.OrderBy VARCHAR(50) = 'NazivUcionice',
@.OrderSort VARCHAR(10) = 'ASC'
AS
DECLARE @.SQLstmt varchar (1000)
SELECT @.SQLstmt = 'SELECT U.ID AS ElementID,
U.Naziv
U.Vrsta,
U.Tip,
U.Vlasnistvo,
U.Aktivna,
U.Broj_mjesta
FROM U
WHERE U.Aktivna=' +@.akt + '
ORDER BY ' + @.OrderBy + ' ' + @.OrderSort
EXEC (@.SQLstmt)
GO
--
Tomislav Bilic
ICQ: 1824223
http://www.escapestudio.net|||Using the Oracle OLEDB Provider, either create a linked server or use
OPENROWSET and specify the connection string.
--
David Portas
SQL Server MVP
--

Dynamic Dates and Stored Procedures

I was wondering if anyone could help me past a problem that I am having using dynamic dates and stored procedures. I am new to this and have the following procedure written, This particular query will only return data if the variables @.sDateFrom and @.sDateTo are surrounded by single quotes however, in the stored procedure the single quotes cause the query to fail giving the error "Syntax error converting datetime from character string"

Any insights would be greatly appreciated.

Thanks,
Brent

ALTER PROC prBuildSelector (
@.sNRW varchar(50),
@.sDateFrom datetime,
@.sDateTo datetime
)
AS

SELECT name,start_date,end_date, sku FROM product
INNER JOIN section ON product.pfid = section.pfid
WHERE product.name LIKE '%@.sNRW%' AND
start_date > @.sDateFrom
AND start_date < @.sDateTo
ORDER BY vcfeb10_product.name
ReturnWhich database are you using ?|||Originally posted by brentb
I was wondering if anyone could help me past a problem that I am having using dynamic dates and stored procedures. I am new to this and have the following procedure written, This particular query will only return data if the variables @.sDateFrom and @.sDateTo are surrounded by single quotes however, in the stored procedure the single quotes cause the query to fail giving the error "Syntax error converting datetime from character string"

Any insights would be greatly appreciated.

Thanks,
Brent

ALTER PROC prBuildSelector (
@.sNRW varchar(50),
@.sDateFrom datetime,
@.sDateTo datetime
)
AS

SELECT name,start_date,end_date, sku FROM product
INNER JOIN section ON product.pfid = section.pfid
WHERE product.name LIKE '%@.sNRW%' AND
start_date > @.sDateFrom
AND start_date < @.sDateTo
ORDER BY vcfeb10_product.name
Return

EXEC('SELECT name,start_date,end_date, sku FROM product
INNER JOIN section ON product.pfid = section.pfid
WHERE product.name LIKE ''%' + @.sNRW + '%'' AND
start_date > @.sDateFrom
AND start_date < @.sDateTo
ORDER BY vcfeb10_product.name')

Dyanamic stored procedures

Hi all,
I'm building a search function and I need do it using stored procedures. I'm searching a table on 'Name' 'email address' and 'key words'. (basically 3 columns). The user must be able to use it as
SELECT ......... WHERE (NAME LIKE @.name) AND/OR (Email LIKE @.email) AND/OR ..........
Can some one tell me how to handle this in a stored procedure. By the way is this approch correct. Please tell me if there is a better method to tackle this situation.
Thanks,
-VJ

I always generate a dynamic SQL statement based on what the user has entered and then execute it or use sp_executesql to run it. Some will say that dynamic SQL is bad because the execution plan doesn't get cached like normal procs, but you can get better performance with dynamic SQL sometimes because since the query criteria can change dramtically, the cached execution plan may be a bad one. Always bets to time and view the execution plan in QA, but my experience is that dynamic SQL within the stored proc works best when there are varying search parameters.

|||If it's just 3 columns you're validating, you should just use case statements instead of utilizing dynamic sql.
selelct * from table where -- normal stuff
Name Like (
CASE
WHEN @.name IS NOT NULL THEN @.name
ELSE Name
END
)

-----
A lot of times, I'll validate a string for nulls and empty strings, so I'll usually have this function in most of my databases.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION [dbo].[IsEmptyString]
(
@.String VarChar(2000)
)
RETURNS BIT AS
BEGIN
DECLARE @.ReturnVar BIT
IF (@.String IS NOT NULL AND @.String <> '') -- you can trim if you are avoiding whitespaces.
SET @.ReturnVar = 0
ELSE
SET @.ReturnVar = 1

RETURN @.ReturnVar
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

|||Test it both ways. I've done it with case statements and gotten terrible performance compared with dynamic SQL. It all depends on the situation. Having 3 or 6 or 20 columns really isn't the issue; it's how the procedure performs when varying numbers or combinations of the criteria are entered. I've seen demos where the dynamic SQL blew away using case or isnull.|||You're right that it's completely situational in terms of performance,and many will do their best to keep the actual logic in code as much aspossible. From personal experience, iterations through decentsized databases (2 million records in each of the major tables), a casestatement will perform fairly well as compared to utilizing dynamicsql. It helps further when you require calculations to be madeper record to assist in it's selection criteria (Distance for example),and the one of the factors is based on the parameters. In caseslike this, a case statement tends to outweigh a dump of string to beexecuted in a stored procedure.
Create Procedure KraGiE.ExecuteString
@.SQL varchar (2000)
AS
EXECUTE( @.SQL )
GO
For some reason, that makes me cringe, and I'm sure many database administrators will plainly say "no."