dynamic procedure creation - possible?

Hi Gurus
I am trying to develop an application around SQL server. The app allows
the user to input a where clause at runtime, which the application runs
against. The only way the app can use the input where clause is by
building an SQL statement including the where clause, and then calling
sp_executeSQL against it. This is inefficient; ideally I would like to
compile the SQL statement (as it will be used multiple times) - but how
can I do this? I cannot call CREATE PROCEDURE, as that cannot be done
from inside another statement (it must be done explicitly with a
hardcoded where statement).
Is there any wat I can improve on building the statement and calling
sp_executeSQL every time I need to run it?"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1169494423.824216.35130@.38g2000cwa.googlegroups.com...
> Hi Gurus
> I am trying to develop an application around SQL server. The app allows
> the user to input a where clause at runtime, which the application runs
> against. The only way the app can use the input where clause is by
> building an SQL statement including the where clause, and then calling
> sp_executeSQL against it. This is inefficient;
Why do you think that? SQL Server will cache and reuse SQL queries sent
using sp_executesql, especially if you use parameter markers in the query.
See
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
http://www.microsoft.com/technet/pr...005/recomp.mspx
Execution Plan Caching and Reuse
http://msdn2.microsoft.com/en-us/library/aa175244(SQL.80).aspx

> ideally I would like to
> compile the SQL statement (as it will be used multiple times) - but how
> can I do this? I cannot call CREATE PROCEDURE, as that cannot be done
> from inside another statement (it must be done explicitly with a
> hardcoded where statement).
> Is there any wat I can improve on building the statement and calling
> sp_executeSQL every time I need to run it?
>
No. That is really a perfectly fine thing to do. Consider using parameter
markers in sp_executesql to get query plan reuse and reduce compilations.
However if the volume of these dynamic queries is not great, or the cost of
each query to execute is large, then plan reuse may not be important or even
desirable.
David|||Thanks very much for that David.
The queries don't have parameters - they are always exactly the same
every time they are run (the user may change the where clause, but this
will be a very infrequent event). However, there could be up to 255 of
these queries (and where clauses), which could potentially all get
called (in series) every time a row is inserted into a certain key
table. A row is inserted in the key table possibly several times a
second (worst case).|||"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1169495992.357049.50780@.m58g2000cwm.googlegroups.com...
> Thanks very much for that David.
> The queries don't have parameters - they are always exactly the same
> every time they are run (the user may change the where clause, but this
> will be a very infrequent event). However, there could be up to 255 of
> these queries (and where clauses), which could potentially all get
> called (in series) every time a row is inserted into a certain key
> table. A row is inserted in the key table possibly several times a
> second (worst case).
>
Ok, 255 different queries isn't much at all. I would just let SQL Server
cache them.
David

dynamic procedure creation - possible?

Hi Gurus
I am trying to develop an application around SQL server. The app allows
the user to input a where clause at runtime, which the application runs
against. The only way the app can use the input where clause is by
building an SQL statement including the where clause, and then calling
sp_executeSQL against it. This is inefficient; ideally I would like to
compile the SQL statement (as it will be used multiple times) - but how
can I do this? I cannot call CREATE PROCEDURE, as that cannot be done
from inside another statement (it must be done explicitly with a
hardcoded where statement).
Is there any wat I can improve on building the statement and calling
sp_executeSQL every time I need to run it?
"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1169494423.824216.35130@.38g2000cwa.googlegrou ps.com...
> Hi Gurus
> I am trying to develop an application around SQL server. The app allows
> the user to input a where clause at runtime, which the application runs
> against. The only way the app can use the input where clause is by
> building an SQL statement including the where clause, and then calling
> sp_executeSQL against it. This is inefficient;
Why do you think that? SQL Server will cache and reuse SQL queries sent
using sp_executesql, especially if you use parameter markers in the query.
See
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
Execution Plan Caching and Reuse
http://msdn2.microsoft.com/en-us/library/aa175244(SQL.80).aspx

> ideally I would like to
> compile the SQL statement (as it will be used multiple times) - but how
> can I do this? I cannot call CREATE PROCEDURE, as that cannot be done
> from inside another statement (it must be done explicitly with a
> hardcoded where statement).
> Is there any wat I can improve on building the statement and calling
> sp_executeSQL every time I need to run it?
>
No. That is really a perfectly fine thing to do. Consider using parameter
markers in sp_executesql to get query plan reuse and reduce compilations.
However if the volume of these dynamic queries is not great, or the cost of
each query to execute is large, then plan reuse may not be important or even
desirable.
David
|||Thanks very much for that David.
The queries don't have parameters - they are always exactly the same
every time they are run (the user may change the where clause, but this
will be a very infrequent event). However, there could be up to 255 of
these queries (and where clauses), which could potentially all get
called (in series) every time a row is inserted into a certain key
table. A row is inserted in the key table possibly several times a
second (worst case).
|||"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1169495992.357049.50780@.m58g2000cwm.googlegro ups.com...
> Thanks very much for that David.
> The queries don't have parameters - they are always exactly the same
> every time they are run (the user may change the where clause, but this
> will be a very infrequent event). However, there could be up to 255 of
> these queries (and where clauses), which could potentially all get
> called (in series) every time a row is inserted into a certain key
> table. A row is inserted in the key table possibly several times a
> second (worst case).
>
Ok, 255 different queries isn't much at all. I would just let SQL Server
cache them.
David
sql

dynamic procedure creation - possible?

Hi Gurus
I am trying to develop an application around SQL server. The app allows
the user to input a where clause at runtime, which the application runs
against. The only way the app can use the input where clause is by
building an SQL statement including the where clause, and then calling
sp_executeSQL against it. This is inefficient; ideally I would like to
compile the SQL statement (as it will be used multiple times) - but how
can I do this? I cannot call CREATE PROCEDURE, as that cannot be done
from inside another statement (it must be done explicitly with a
hardcoded where statement).
Is there any wat I can improve on building the statement and calling
sp_executeSQL every time I need to run it?"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1169494423.824216.35130@.38g2000cwa.googlegroups.com...
> Hi Gurus
> I am trying to develop an application around SQL server. The app allows
> the user to input a where clause at runtime, which the application runs
> against. The only way the app can use the input where clause is by
> building an SQL statement including the where clause, and then calling
> sp_executeSQL against it. This is inefficient;
Why do you think that? SQL Server will cache and reuse SQL queries sent
using sp_executesql, especially if you use parameter markers in the query.
See
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
Execution Plan Caching and Reuse
http://msdn2.microsoft.com/en-us/library/aa175244(SQL.80).aspx
> ideally I would like to
> compile the SQL statement (as it will be used multiple times) - but how
> can I do this? I cannot call CREATE PROCEDURE, as that cannot be done
> from inside another statement (it must be done explicitly with a
> hardcoded where statement).
> Is there any wat I can improve on building the statement and calling
> sp_executeSQL every time I need to run it?
>
No. That is really a perfectly fine thing to do. Consider using parameter
markers in sp_executesql to get query plan reuse and reduce compilations.
However if the volume of these dynamic queries is not great, or the cost of
each query to execute is large, then plan reuse may not be important or even
desirable.
David|||Thanks very much for that David.
The queries don't have parameters - they are always exactly the same
every time they are run (the user may change the where clause, but this
will be a very infrequent event). However, there could be up to 255 of
these queries (and where clauses), which could potentially all get
called (in series) every time a row is inserted into a certain key
table. A row is inserted in the key table possibly several times a
second (worst case).|||"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1169495992.357049.50780@.m58g2000cwm.googlegroups.com...
> Thanks very much for that David.
> The queries don't have parameters - they are always exactly the same
> every time they are run (the user may change the where clause, but this
> will be a very infrequent event). However, there could be up to 255 of
> these queries (and where clauses), which could potentially all get
> called (in series) every time a row is inserted into a certain key
> table. A row is inserted in the key table possibly several times a
> second (worst case).
>
Ok, 255 different queries isn't much at all. I would just let SQL Server
cache them.
David

Dynamic PIVOT quey challange

Hi

I have following Table A In which values of LotNo field is not fixed so we cannot write these values to our PIVOT clause in Select statement. Now can any one provide me sucha a query which can generate this PIVOT result in Table B. Not there is a distnict list of Lotno. is availble from a view.

Table A

LotNo.

Pcs

Wt.

12

200

12.212

13

21

16.214

17

23

21.211

18

27

32.212

Table B

LotNo

12

13

17

18

Pcs

200

21

23

27

Weight

12.212

16.214

21.211

32.212

So I want to write a select query wth PIVOT clause which takes Lotno. which is not fixed values (As given in Adventureworks sample). ThisLotno. is based on distinct values from a list of LotNo. which is availble in another Table. Please anyone help me.

Nilkanth Desai

You can use the following querys

1. Using UNION

SELECT
'Pcs',
*
FROM
(SELECT LotNo,Pcs from TableA) Master
PIVOT
(
Sum(Pcs) for LotNo in ([12],[13],[17],[18])
) AS pvt2
Union All
SELECT
'Wt',
*
FROM
(SELECT LotNo,Wt from TableA) Master
PIVOT
(
Sum(Wt) for LotNo in ([12],[13],[17],[18])
) AS pvt2

2. Merging data into Single Table then applying the Pivot

Declare @.Table Table
(
LotNo int,
Type varchar(10),
Value float
)

Insert Into @.Table Select Lotno,'Pcs', Pcs From TableA;
Insert Into @.Table Select LotNo,'Wt', Wt From TableA;

SELECT
*
FROM
(SELECT LotNo,Type,Value from @.Table) Master
PIVOT
(
Sum(Value) for LotNo in ([12],[13],[17],[18])
) AS pvt2

To generate this query dynamically you can see my earlier post http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1006316&SiteID=1

|||

Here you go:

-- Create a Temporary Table holding all Values As LotNo, Group, Value
SELECT LotNo AS LotNo, 'Pcs' as Grp, Pcs as Value INTO #T1 FROM TableA
UNION
SELECT LotNo AS LotNo, 'Weight' as Grp, Weight as Value FROM TableA;

-- Declare a Table holding all Values of LotNo
DECLARE @.T2 AS TABLE(LotNo INT);
INSERT INTO @.T2 SELECT DISTINCT LotNo FROM #T1;

-- Construct the Columns for the SELECT and PIVOT-Clause
DECLARE
@.cols AS NVARCHAR(MAX),
@.lotno AS INT,
@.sql AS NVARCHAR(MAX);

SET @.lotno = (SELECT MIN(LotNo) FROM @.T2);
SET @.cols = '';
WHILE @.lotno IS NOT NULL
BEGIN
SET @.cols = @.cols + ', ' + QUOTENAME(@.lotno)
SET @.lotno = (SELECT MIN(LotNo) FROM @.T2 WHERE LotNo > @.lotno)
END
SET @.cols = SUBSTRING(@.cols, 3, LEN(@.cols));

-- Construct the SQL Statement
SET @.sql = 'SELECT Grp, ' + @.cols + '
FROM #T1
PIVOT(MAX(Value)
FOR LotNo IN (' + @.cols + ')) AS P'

-- Run it
EXEC sp_executesql @.sql;

-- Cleanup
DROP TABLE #T1;

sql

Dynamic Order By Clause

I'm trying to dynamically determine the Order By clause, like I would in VB,
but I can't figure out the syntax. This is my SP. Thanks
CREATE PROCEDURE [dbo].[usp_Search_MessageBoard]
@.mForumID int,
@.mSearchValue varchar(100),
@.mOrderBy varchar(20)
AS
Declare @.OrderByField as varchar(20)
Select @.OrderByField =
CASE
WHEN @.mOrderBy = "" THEN 'MB_Posts.ID'
WHEN @.mOrderBy = "LastPostDesc" THEN ' MB_Posts.Last_Post DESC'
WHEN @.mOrderBy = "LastPost" THEN ' MB_Posts.Last_Post'
WHEN @.mOrderBy = "AuthorDesc" THEN ' users.UName DESC'
WHEN @.mOrderBy = "Author" THEN ' users.UName'
WHEN @.mOrderBy = "RepliesDesc" THEN 'MB_Posts.Replies DESC'
WHEN @.mOrderBy = "Replies" THEN 'MB_Posts.Replies'
WHEN @.mOrderBy = "TopicDesc" THEN 'MB_Posts.Subject DESC'
WHEN @.mOrderBy = "Topic" THEN ' MB_Posts.Subject'
ELSE 'MB_Posts.ID!'
END
SELECT MB_Posts.ID, MB_Posts.Forum_ID, MB_Posts.Subject, MB_Posts.UID,
MB_Posts.Replies, MB_Posts.Last_Post, users.UName, Icons.Image
FROM USERS
INNER JOIN MB_Posts ON users.UID = MB_Posts.UID
INNER JOIN Icons ON users.Icon_ID = Icons.ID
WHERE MB_Posts.Display = 1
AND Thread = 0
AND MB_Posts.AdminMsg = 0
AND Forum_ID= @.mForumID
AND Body LIKE '%' + @.mSearchValue + '%'
ORDER BY @.OrderByField
GOHi,
See the below URL:-
http://www.sommarskog.se/dynamic_sql.html#Order_by
Thanks
Hari
SQL Server MVP
"Phill" <Phill@.discussions.microsoft.com> wrote in message
news:F1BF6BDD-C08C-4EBA-993B-0AF6080516C9@.microsoft.com...
> I'm trying to dynamically determine the Order By clause, like I would in
> VB,
> but I can't figure out the syntax. This is my SP. Thanks
> CREATE PROCEDURE [dbo].[usp_Search_MessageBoard]
> @.mForumID int,
> @.mSearchValue varchar(100),
> @.mOrderBy varchar(20)
> AS
> Declare @.OrderByField as varchar(20)
> Select @.OrderByField =
> CASE
> WHEN @.mOrderBy = "" THEN 'MB_Posts.ID'
> WHEN @.mOrderBy = "LastPostDesc" THEN ' MB_Posts.Last_Post DESC'
> WHEN @.mOrderBy = "LastPost" THEN ' MB_Posts.Last_Post'
> WHEN @.mOrderBy = "AuthorDesc" THEN ' users.UName DESC'
> WHEN @.mOrderBy = "Author" THEN ' users.UName'
> WHEN @.mOrderBy = "RepliesDesc" THEN 'MB_Posts.Replies DESC'
> WHEN @.mOrderBy = "Replies" THEN 'MB_Posts.Replies'
> WHEN @.mOrderBy = "TopicDesc" THEN 'MB_Posts.Subject DESC'
> WHEN @.mOrderBy = "Topic" THEN ' MB_Posts.Subject'
> ELSE 'MB_Posts.ID!'
> END
>
> SELECT MB_Posts.ID, MB_Posts.Forum_ID, MB_Posts.Subject, MB_Posts.UID,
> MB_Posts.Replies, MB_Posts.Last_Post, users.UName, Icons.Image
> FROM USERS
> INNER JOIN MB_Posts ON users.UID = MB_Posts.UID
> INNER JOIN Icons ON users.Icon_ID = Icons.ID
> WHERE MB_Posts.Display = 1
> AND Thread = 0
> AND MB_Posts.AdminMsg = 0
> AND Forum_ID= @.mForumID
> AND Body LIKE '%' + @.mSearchValue + '%'
> ORDER BY @.OrderByField
>
> GO
>|||Order By clause requires a literal string. You might want to take a look at
Erland's article for some info/work around.
http://www.sommarskog.se/dynamic_sql.html#Order_by
-oj
"Phill" <Phill@.discussions.microsoft.com> wrote in message
news:F1BF6BDD-C08C-4EBA-993B-0AF6080516C9@.microsoft.com...
> I'm trying to dynamically determine the Order By clause, like I would in
> VB,
> but I can't figure out the syntax. This is my SP. Thanks
> CREATE PROCEDURE [dbo].[usp_Search_MessageBoard]
> @.mForumID int,
> @.mSearchValue varchar(100),
> @.mOrderBy varchar(20)
> AS
> Declare @.OrderByField as varchar(20)
> Select @.OrderByField =
> CASE
> WHEN @.mOrderBy = "" THEN 'MB_Posts.ID'
> WHEN @.mOrderBy = "LastPostDesc" THEN ' MB_Posts.Last_Post DESC'
> WHEN @.mOrderBy = "LastPost" THEN ' MB_Posts.Last_Post'
> WHEN @.mOrderBy = "AuthorDesc" THEN ' users.UName DESC'
> WHEN @.mOrderBy = "Author" THEN ' users.UName'
> WHEN @.mOrderBy = "RepliesDesc" THEN 'MB_Posts.Replies DESC'
> WHEN @.mOrderBy = "Replies" THEN 'MB_Posts.Replies'
> WHEN @.mOrderBy = "TopicDesc" THEN 'MB_Posts.Subject DESC'
> WHEN @.mOrderBy = "Topic" THEN ' MB_Posts.Subject'
> ELSE 'MB_Posts.ID!'
> END
>
> SELECT MB_Posts.ID, MB_Posts.Forum_ID, MB_Posts.Subject, MB_Posts.UID,
> MB_Posts.Replies, MB_Posts.Last_Post, users.UName, Icons.Image
> FROM USERS
> INNER JOIN MB_Posts ON users.UID = MB_Posts.UID
> INNER JOIN Icons ON users.Icon_ID = Icons.ID
> WHERE MB_Posts.Display = 1
> AND Thread = 0
> AND MB_Posts.AdminMsg = 0
> AND Forum_ID= @.mForumID
> AND Body LIKE '%' + @.mSearchValue + '%'
> ORDER BY @.OrderByField
>
> GO
>|||Thanks for pointing me in the right direction. I have different data types,
so I had to use the second format. The only problem is that it won't sort i
n
DESC order. This is what is looks like now. Any suggestions?
CREATE PROCEDURE [dbo].[usp_Search_MessageBoard]
@.mForumID int,
@.mSearchValue varchar(100) =null,
@.mOrderBy varchar(20)=null
AS
IF RIGHT(@.mOrderBy,4)='Desc'
SELECT MB_Posts.ID, MB_Posts.Forum_ID, MB_Posts.Subject, MB_Posts.UID,
MB_Posts.Replies, MB_Posts.Last_Post, users.UName, Icons.Image
FROM USERS
INNER JOIN MB_Posts ON users.UID = MB_Posts.UID
INNER JOIN Icons ON users.Icon_ID = Icons.ID
WHERE MB_Posts.Display = 1
AND Thread = 0
AND MB_Posts.AdminMsg = 0
AND Forum_ID= @.mForumID
AND Body LIKE '%' + @.mSearchValue + '%'
ORDER BY CASE @.mOrderBy WHEN NULL THEN MB_Posts.ID ELSE NULL END,
CASE @.mOrderBy WHEN 'LastPostDesc' THEN MB_Posts.Last_Post
ELSE NULL END,
CASE @.mOrderBy WHEN 'AuthorDesc' THEN users.UName ELSE
NULL END,
CASE @.mOrderBy WHEN 'RepliesDesc' THEN MB_Posts.Replies
ELSE NULL END,
CASE @.mOrderBy WHEN 'TopicDesc' THEN MB_Posts.Subject ELSE
NULL END
DESC
ELSE
SELECT MB_Posts.ID, MB_Posts.Forum_ID, MB_Posts.Subject, MB_Posts.UID,
MB_Posts.Replies, MB_Posts.Last_Post, users.UName, Icons.Image
FROM USERS
INNER JOIN MB_Posts ON users.UID = MB_Posts.UID
INNER JOIN Icons ON users.Icon_ID = Icons.ID
WHERE MB_Posts.Display = 1
AND Thread = 0
AND MB_Posts.AdminMsg = 0
AND Forum_ID= @.mForumID
AND Body LIKE '%' + @.mSearchValue + '%'
ORDER BY CASE @.mOrderBy WHEN NULL THEN MB_Posts.ID ELSE NULL END,
CASE @.mOrderBy WHEN 'LastPost' THEN MB_Posts.Last_Post
ELSE NULL END,
CASE @.mOrderBy WHEN 'Author' THEN users.UName ELSE NULL END,
CASE @.mOrderBy WHEN 'Replies' THEN MB_Posts.Replies ELSE
NULL END,
CASE @.mOrderBy WHEN 'Topic' THEN MB_Posts.Subject ELSE
NULL END
ASC
"Phill" wrote:

> I'm trying to dynamically determine the Order By clause, like I would in V
B,
> but I can't figure out the syntax. This is my SP. Thanks
> CREATE PROCEDURE [dbo].[usp_Search_MessageBoard]
> @.mForumID int,
> @.mSearchValue varchar(100),
> @.mOrderBy varchar(20)
> AS
> Declare @.OrderByField as varchar(20)
> Select @.OrderByField =
> CASE
> WHEN @.mOrderBy = "" THEN 'MB_Posts.
ID'
> WHEN @.mOrderBy = "LastPostDesc" THEN ' MB_Posts.Last_Post DESC'
> WHEN @.mOrderBy = "LastPost" THEN ' MB_Posts.Last_Pos
t'
> WHEN @.mOrderBy = "AuthorDesc" THEN ' users.UName DESC'
> WHEN @.mOrderBy = "Author" THEN ' users.UName'
> WHEN @.mOrderBy = "RepliesDesc" THEN 'MB_Posts.Replies DESC'
> WHEN @.mOrderBy = "Replies" THEN 'MB_Posts.Repli
es'
> WHEN @.mOrderBy = "TopicDesc" THEN 'MB_Posts.Subject DESC'
> WHEN @.mOrderBy = "Topic" THEN ' MB_Posts.Subjec
t'
> ELSE 'MB_Posts.ID!'
> END
>
> SELECT MB_Posts.ID, MB_Posts.Forum_ID, MB_Posts.Subject, MB_Posts.UID,
> MB_Posts.Replies, MB_Posts.Last_Post, users.UName, Icons.Image
> FROM USERS
> INNER JOIN MB_Posts ON users.UID = MB_Posts.UID
> INNER JOIN Icons ON users.Icon_ID = Icons.ID
> WHERE MB_Posts.Display = 1
> AND Thread = 0
> AND MB_Posts.AdminMsg = 0
> AND Forum_ID= @.mForumID
> AND Body LIKE '%' + @.mSearchValue + '%'
> ORDER BY @.OrderByField
>
> GO
>

Dynamic IN clause

I have a "can this be done" question...
I currently have a stored procedure that builds a string into a SQL
Statement and runs it.
This is an abreviated version as an example:
CREATE PROCEDURE stp_ClaimInfo
@.DFTAX# VARCHAR(12),
@.TCLAIM varchar(1000)
AS
BEGIN
DECLARE @.SQLstr varchar(4000)
set @.SQLstr = ' SELECT TMBR#, TCLAIM, SUM(TFLD16) AS TTFLD16 '
+ ' FROM tbl_claim_status'
+ ' WHERE DFTAX# = ''' + @.DFTAX# + ''''
+ ' AND TCLAIM IN (' + @.TCLAIM + ')'
+ ' GROUP BY TMBR#, TCLAIM'
exec(@.SQLstr)
END
And the run it as:
EXEC stp_ClaimInfo '86- 0291651','606900122,606900121,606800078'
The reason is because of the "IN" cluase. I need to pass it claim numbers,
but there can be one or more at a time, and different each time it is run.
In effect, the above query ends up as:
SELECT TMBR#, TCLAIM, SUM(TFLD16) AS TTFLD16
FROM tbl_claim_status
WHERE DFTAX# = '86-0291651'
AND TCLAIM IN (606900122,606900121,606800078)
GROUP BY TMBR#, TCLAIM
My question is how can I pass a dynamic list of values for the IN clause but
not have to write the statement as a string then execute it using
"exec(@.SQLstr)"? Can this be done?
-- AndrewUse the sp_executesql , this way you can add parameter definition. This
is an example i used in an SP with OUTPUT parameters.
---
USE OUTPUT PARAM
http://support.microsoft.com/defaul...B;EN-US;q262499
---
DECLARE @.v_sql = NVARCHAR(4000)
SET @.v_sql = N'SELECT @.v_totalrowcountOUT = isnull(count(1),0) FROM
'+@.v_tablename_vc
SET @.ParmDefinition = N'@.v_totalrowcountOUT INT OUTPUT '
EXECUTE sp_executesql @.v_sql , @.ParmDefinition , @.v_totalrowcountOUT =@.
v_totalrowcount OUTPUT
---
USE INPUT PARAM
---
/* Build the SQL string once.*/
DECLARE @.SQLString NVARCHAR(4000)
DECLARE @.IntVariable INT
SET @.IntVariable = 100
SET @.SQLString = N'SELECT * FROM pubs.dbo.employee WHERE job_lvl =
@.level'
SET @.ParmDefinition = N'@.level tinyint'
/* Execute the string with the first parameter value. */
SET @.IntVariable = 35
EXECUTE sp_executesql @.SQLString, @.ParmDefinition, @.level = @.IntVariable|||> My question is how can I pass a dynamic list of values for the IN clause
> but not have to write the statement as a string then execute it using
> "exec(@.SQLstr)"? Can this be done?
SQL Server does not know what an array is, so no, you can't fool it into
believing this string is an array of ints. You can, however, encapsulate
splitting functionality off into another object.
http://www.aspfaq.com/2248
A|||You could do something like
AND ',' + @.TCLAIM + ',' LIKE '%,' + TCLAIM + ',%'
its not pretty though
"Andrew" wrote:

> I have a "can this be done" question...
> I currently have a stored procedure that builds a string into a SQL
> Statement and runs it.
> This is an abreviated version as an example:
> CREATE PROCEDURE stp_ClaimInfo
> @.DFTAX# VARCHAR(12),
> @.TCLAIM varchar(1000)
> AS
> BEGIN
> DECLARE @.SQLstr varchar(4000)
> set @.SQLstr = ' SELECT TMBR#, TCLAIM, SUM(TFLD16) AS TTFLD16 '
> + ' FROM tbl_claim_status'
> + ' WHERE DFTAX# = ''' + @.DFTAX# + ''''
> + ' AND TCLAIM IN (' + @.TCLAIM + ')'
> + ' GROUP BY TMBR#, TCLAIM'
> exec(@.SQLstr)
> END
> And the run it as:
> EXEC stp_ClaimInfo '86- 0291651','606900122,606900121,606800078'
> The reason is because of the "IN" cluase. I need to pass it claim numbers
,
> but there can be one or more at a time, and different each time it is run.
> In effect, the above query ends up as:
> SELECT TMBR#, TCLAIM, SUM(TFLD16) AS TTFLD16
> FROM tbl_claim_status
> WHERE DFTAX# = '86-0291651'
> AND TCLAIM IN (606900122,606900121,606800078)
> GROUP BY TMBR#, TCLAIM
>
> My question is how can I pass a dynamic list of values for the IN clause b
ut
> not have to write the statement as a string then execute it using
> "exec(@.SQLstr)"? Can this be done?
> -- Andrew
>
>|||Andrew (AndrewR2k1@.hotmail.com) writes:
> My question is how can I pass a dynamic list of values for the IN clause
> but not have to write the statement as a string then execute it using
> "exec(@.SQLstr)"? Can this be done?
Indeed, this can be done in a multitude of ways. For a quick start,
look at http://www.sommarskog.se/arrays-in-...ist-of-strings.
To see more ways, read the rest of the article.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||dizzler (john.dacosta@.gmail.com) writes:
> Use the sp_executesql , this way you can add parameter definition. This
> is an example i used in an SP with OUTPUT parameters.
sp_executesql does not change matters here. You would still have
to interpolate the list of values into the SQL string, you cannot
pass it as a parameter to sp_executesql.
The correct answer is that this is a task that you should not use
dynamic SQL at all for.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||You do not understand SQL at all and want to keep writing BASIC. And
you use tbl- prfixes, # in names and what look like dat type prefixes
on column names. And put it in uppercase to make it harder to read. I
also loved seeing "fld_16" vague and it lets us know that you still
thinkin fields nad do not know about relational columns. You cannot
have a table named "_status" because a status is an atrribute not an
entity.
CREATE PROCEDURE GetClaimsList
(@.my_tax_nbr CHAR(10), @.p01 CHAR(9), @.p02 CHAR(9), .., @.p50 CHAR(9))
AS
SELECT member_nbr, claim_nbr SUM(fld_6) AS foobar_total
FROM Claims
WHERE df_tax_nbr = @.my_tax_nbr
AND claim_nbr IN (@.p01 CHAR(9), @.p02 CHAR(9), .. , @.p50 CHAR(9))
GROUP BY member_nbr, claim_nbr ;
Did you know that a T-SQL proc can have over 1000 parameters? Do you
eer need more than that? Wow! Compiled code that ports easily.|||dizzler,
Thank you for the reply, but I feel you missed the point of my question.
-- Andrew
"dizzler" <john.dacosta@.gmail.com> wrote in message
news:1143668337.316099.27080@.i40g2000cwc.googlegroups.com...
> Use the sp_executesql , this way you can add parameter definition. This
> is an example i used in an SP with OUTPUT parameters.
> ---
> USE OUTPUT PARAM
> http://support.microsoft.com/defaul...B;EN-US;q262499
> ---
> DECLARE @.v_sql = NVARCHAR(4000)
> SET @.v_sql = N'SELECT @.v_totalrowcountOUT = isnull(count(1),0) FROM
> '+@.v_tablename_vc
> SET @.ParmDefinition = N'@.v_totalrowcountOUT INT OUTPUT '
> EXECUTE sp_executesql @.v_sql , @.ParmDefinition , @.v_totalrowcountOUT =@.
> v_totalrowcount OUTPUT
>
> ---
> USE INPUT PARAM
> ---
> /* Build the SQL string once.*/
> DECLARE @.SQLString NVARCHAR(4000)
> DECLARE @.IntVariable INT
> SET @.IntVariable = 100
> SET @.SQLString = N'SELECT * FROM pubs.dbo.employee WHERE job_lvl =
> @.level'
> SET @.ParmDefinition = N'@.level tinyint'
> /* Execute the string with the first parameter value. */
> SET @.IntVariable = 35
> EXECUTE sp_executesql @.SQLString, @.ParmDefinition, @.level = @.IntVariable
>|||Phillip,
Thank you for the reply, but your suggestion just wouldn't work for the
situation I have as the incoming string is a comma deliminated list and
trying to break it apart outside the SQL Statement would reduce the
efficiency of the whole process. In a different situation, your suggestion
could prove to be a useful answer.
-- Andrew
"Phillip Wilson" <Phillip Wilson@.discussions.microsoft.com> wrote in message
news:12B92F8B-101B-4184-8351-365B1D786293@.microsoft.com...
> You could do something like
> AND ',' + @.TCLAIM + ',' LIKE '%,' + TCLAIM + ',%'
> its not pretty though
> "Andrew" wrote:
>|||Aaron,
Thank you for the reply, this is the answer I was looking for...fantastic
ideas here. Thank ya much!
-- Andrew
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uGW%23Zo3UGHA.1688@.TK2MSFTNGP11.phx.gbl...
> SQL Server does not know what an array is, so no, you can't fool it into
> believing this string is an array of ints. You can, however, encapsulate
> splitting functionality off into another object.
> http://www.aspfaq.com/2248
> A
>

Dynamic From clause?

How can I get an SP to use one table in the From clause if a condition is true and another table if false? I'd like to avoid creating a "dynamic query," one in which the whole query is built in a string then executed (exec str).

Thank you.

There isn't a really good way.

To avoid Dynamic SQL, the only option is to use an IF statement in your stored procedure to send processing to one select or the other.

If the tables are very similar, you can combine them into a single VIEW and query against that, adjusting the WHERE clause instead (which supports variables).

-Ryan / Kardax

|||That's what I was afraid of... Thank you.|||

You can use 2 separate queries for each condition and combine them with UNION. The fields in the SELECT statement must be similar in this case. Here is an example:

Code Snippet

SELECT col1, col2, col3

FROM Table1

WHERE Condition1

UNION

SELECT col1, col2, col3

FROM Table2

WHERE Condition2

I hope this answers your questions.

Best regards,

Sami Samir

Dynamic From Clause

Hi,
Q: Is there a way to make the table's name in the from clause dynamic?
I have a job (T-SQL statements) that makes copies of current month's
selected tables to our archive DB. All copies will have a time stamp like
TableName_yyyy_mm. What I always do is that I edit the T-SQL statements in
that job to reflect the current year and the current month for all statement
s
(find and replace of course).
This month will be,
Select * into archive.dbo.PEIF_2005_03 from data.dbo.PEIF
Next month will be,
Select * into archive.dbo.PEIF_2005_04 from data.dbo.PEIF
.. and so on
I have tried using variables for the time stamp, like:
Declare @.Year Int
Declare @.Month Int
Select @.Year=(
select
case
when month(getdate()) - 1 = 0 then year(getdate()) - 1
else year(getdate())
end
)
Select @.Month=(
select
case
when month(getdate()) - 1 = 0 then 12
else month(getdate()) - 1
end
)
Select * into archive.dbo.PEIF_@.yyyy_@.mm from data.dbo.PEIF (DOESN'T WORK)
Apparently @.variable only works for where clauses (at least in my case).
What I want (if possible) is that I don't need to update the job every month
and let the job does the work for me.
Please help,
Adrian T> Q: Is there a way to make the table's name in the from clause dynamic?
http://www.sommarskog.se/dynamic_sql.html|||you would want to look into the stored procedure sp_executesql in BOL.
Basically it allows you to say "exec sp_executesql @.mySQL" and whatever
is in the string of @.MySQL you've just built up it will generate an
execution plan for it, then run it.
Cheers
Will|||ur solution is almost there.
try this.. a small change.. hope this helps.
Declare @.Year Int
Declare @.Month Int
Select @.Year=(
select
case
when month(getdate()) - 1 = 0 then year(getdate()) - 1
else year(getdate())
end
)
Select @.Month=(
select
case
when month(getdate()) - 1 = 0 then 12
else month(getdate()) - 1
end
)
exec('Select * into archive.dbo.PEIF_' + @.yyyy + '_' + @.mm + ' from
data.dbo.PEIF')

Dyanamic Where Clause

Can anyone advise how i resolve this in SQL, I have the following Stored
procedure :
CREATE PROCEDURE sp_jp_report_won_lost_open_test
@.datestart as varchar(12),
@.dateend as varchar(12),
@.Region as varchar(25), -- e.g. Scotland
@.Territory as int, --ie 901232
@.ReportType as Varchar(7), -- eg. Won or Lost or Ordered
@.mv as money, -- eg Minimum Value of Project
@.mx as money -- eg Maximum Value of Project
AS
set nocount on
If rtrim(@.ReportType)='Open' and LEN(rtrim(@.Region)) = 1
Begin
SELECT t.Description, p.LISANo, p.CreatedDate, p.FollowUpDate,
p.ExpectedOrderDate, p.Name, p.Status, p.ProjectStatus, q.TotalValue,
p.SpecifierCompanyDisplayName, p.Notes
FROM dbo.TblProject p INNER JOIN
dbo.TblTerritory t ON p.TerritoryCode = t.Code LEFT
OUTER JOIN
(SELECT LISANo, MIN(QuoteDate) QuoteDate,
MIN(QuoteNo) QuoteNo
FROM tblquotation_header
WHERE (TblQuotation_Header.TotalValue <=
@.mx AND TblQuotation_Header.TotalValue >= @.mv)
GROUP BY LISANo) x ON x.LISANo = p.LISANo LEFT
OUTER JOIN
dbo.TblQuotation_Header q ON x.QuoteDate = q.QuoteDate
AND x.QuoteNo = q.QuoteNo AND p.LISANo = q.LISANo
WHERE (p.Status = 'Active') AND (p.ProjectStatus = 'Open') AND
(p.CreatedDate >= CONVERT(DATETIME, @.DateStart, 102)) AND
(p.CreatedDate <= CONVERT(DATETIME, @.DateEnd, 102))
AND (t.CurrencySymbol = @.Region)
ORDER BY t.Description
End
If @.mv>0 then i want the additional clause "WHERE (is not null q.TotalValue)
" included in the SQL statement, how do i acheive this ?
Thanks in anticipation
JohnJohn
http://www.sommarskog.se/dyn-search.html
"John" <topguy75@.hotmail.com> wrote in message
news:43a954bf$0$23290$db0fefd9@.news.zen.co.uk...
> Can anyone advise how i resolve this in SQL, I have the following Stored
> procedure :
> CREATE PROCEDURE sp_jp_report_won_lost_open_test
> @.datestart as varchar(12),
> @.dateend as varchar(12),
> @.Region as varchar(25), -- e.g. Scotland
> @.Territory as int, --ie 901232
> @.ReportType as Varchar(7), -- eg. Won or Lost or Ordered
> @.mv as money, -- eg Minimum Value of Project
> @.mx as money -- eg Maximum Value of Project
> AS
> set nocount on
> If rtrim(@.ReportType)='Open' and LEN(rtrim(@.Region)) = 1
> Begin
> SELECT t.Description, p.LISANo, p.CreatedDate, p.FollowUpDate,
> p.ExpectedOrderDate, p.Name, p.Status, p.ProjectStatus, q.TotalValue,
> p.SpecifierCompanyDisplayName, p.Notes
> FROM dbo.TblProject p INNER JOIN
> dbo.TblTerritory t ON p.TerritoryCode = t.Code LEFT
> OUTER JOIN
> (SELECT LISANo, MIN(QuoteDate) QuoteDate,
> MIN(QuoteNo) QuoteNo
> FROM tblquotation_header
> WHERE (TblQuotation_Header.TotalValue <=
> @.mx AND TblQuotation_Header.TotalValue >= @.mv)
> GROUP BY LISANo) x ON x.LISANo = p.LISANo LEFT
> OUTER JOIN
> dbo.TblQuotation_Header q ON x.QuoteDate =
> q.QuoteDate AND x.QuoteNo = q.QuoteNo AND p.LISANo = q.LISANo
> WHERE (p.Status = 'Active') AND (p.ProjectStatus = 'Open') AND
> (p.CreatedDate >= CONVERT(DATETIME, @.DateStart, 102)) AND
> (p.CreatedDate <= CONVERT(DATETIME, @.DateEnd, 102))
> AND (t.CurrencySymbol = @.Region)
> ORDER BY t.Description
> End
>
> If @.mv>0 then i want the additional clause "WHERE (is not null
> q.TotalValue) " included in the SQL statement, how do i acheive this ?
> Thanks in anticipation
> John
>|||Use dynamic SQL
Look at
http://www.sommarskog.se/dynamic_sql.html
Regards
Amish|||"John" <topguy75@.hotmail.com> wrote in message
news:43a954bf$0$23290$db0fefd9@.news.zen.co.uk...
> Can anyone advise how i resolve this in SQL, I have the following Stored
> procedure :
> CREATE PROCEDURE sp_jp_report_won_lost_open_test
> @.datestart as varchar(12),
> @.dateend as varchar(12),
> @.Region as varchar(25), -- e.g. Scotland
> @.Territory as int, --ie 901232
> @.ReportType as Varchar(7), -- eg. Won or Lost or Ordered
> @.mv as money, -- eg Minimum Value of Project
> @.mx as money -- eg Maximum Value of Project
> AS
> set nocount on
> If rtrim(@.ReportType)='Open' and LEN(rtrim(@.Region)) = 1
> Begin
> SELECT t.Description, p.LISANo, p.CreatedDate, p.FollowUpDate,
> p.ExpectedOrderDate, p.Name, p.Status, p.ProjectStatus, q.TotalValue,
> p.SpecifierCompanyDisplayName, p.Notes
> FROM dbo.TblProject p INNER JOIN
> dbo.TblTerritory t ON p.TerritoryCode = t.Code LEFT
> OUTER JOIN
> (SELECT LISANo, MIN(QuoteDate) QuoteDate,
> MIN(QuoteNo) QuoteNo
> FROM tblquotation_header
> WHERE (TblQuotation_Header.TotalValue <=
> @.mx AND TblQuotation_Header.TotalValue >= @.mv)
> GROUP BY LISANo) x ON x.LISANo = p.LISANo LEFT
> OUTER JOIN
> dbo.TblQuotation_Header q ON x.QuoteDate =
> q.QuoteDate AND x.QuoteNo = q.QuoteNo AND p.LISANo = q.LISANo
> WHERE (p.Status = 'Active') AND (p.ProjectStatus = 'Open') AND
> (p.CreatedDate >= CONVERT(DATETIME, @.DateStart, 102)) AND
> (p.CreatedDate <= CONVERT(DATETIME, @.DateEnd, 102))
> AND (t.CurrencySymbol = @.Region)
> ORDER BY t.Description
> End
>
> If @.mv>0 then i want the additional clause "WHERE (is not null
> q.TotalValue) " included in the SQL statement, how do i acheive this ?
> Thanks in anticipation
> John
One quick fix could be this:
IF @.mv>0
SELECT ...
WHERE...
AND q.TotalValue is not null
..
ELSE
SELECT "your query as is"|||where ...
and ((@.mv<=0) or (q.TotalValue is not null) )|||Thanks Alex, simple but effective :-)
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1135183436.404303.48580@.z14g2000cwz.googlegroups.com...
> where ...
> and ((@.mv<=0) or (q.TotalValue is not null) )
>