Dynamically populating an IN() clause within an SSIS package.

Hi,

I currently have a list of User IDs (in a flat file) and I need to connect to a database I have read-only access to, so that I can retrieve additional data about these users.

I imagined a package that ran a query something like:

SELECT * FROM table WHERE UserID IN (<dynamically populated from flat file>).

Can somebody give me some advice as to how I can achieve this (either the way I suggested or another way).

Kind Regards,

Adam.

First thought would be to do it on the server if you have access to the file. Create a temp table bulk insert the file into it then join to the temp table for the query.|||I think the simplest solution is to use a script task to read your file and create the list of users for an IN clause as you mention above. You'd put the list into a variable, and then build your query in an expression-based variable, and set your ole db source's data access mode to "sql command from variable".

Maybe the script would look something like this:

Code Snippet

Public Sub Main()
Dim UserNames() As String = System.IO.File.ReadAllLines(Dts.Variables("FileName").Value.ToString())
Dim s As New System.Text.StringBuilder
Dim IsFirst As Boolean = True
For Each UserName As String In UserNames
If Not IsFirst Then
s.Append(",")
End If
s.Append("'")
s.Append(UserName)
s.Append("'")
IsFirst = False
Next
Dts.Variables("InList").Value = s.ToString()
'Windows.Forms.MessageBox.Show("InList = " + Dts.Variables("InList").Value.ToString())
Dts.TaskResult = Dts.Results.Success
End Sub

|||

2 more:

Have a for each loop to iterate through the file to get each value; then inside of the conatiner have the query logic using and equi-join (=). The thing is that you would run the query as many times as values in the file Have an script task to read the file and build the query; put it in a variable; then you can use that variable as the source uof your query in a Excute SQL task or OLE DB source component.|||

AdamSQLMan wrote:

Hi,

I currently have a list of User IDs (in a flat file) and I need to connect to a database I have read-only access to, so that I can retrieve additional data about these users.

I imagined a package that ran a query something like:

SELECT * FROM table WHERE UserID IN (<dynamically populated from flat file>).

Can somebody give me some advice as to how I can achieve this (either the way I suggested or another way).

Kind Regards,

Adam.

There are many ways to accomplish this. My first thought is this:

Use a data flow to load the flat file, and output it to a data reader destination. Then use a script task to process the data reader into a comma delimited string, and use that in an expression to build your query for use in a second data flow.

|||

NigelRivett wrote:

First thought would be to do it on the server if you have access to the file. Create a temp table bulk insert the file into it then join to the temp table for the query.

If the number of user name is large, then this is probably better than using an IN statement.
|||

JayH wrote:

NigelRivett wrote:

First thought would be to do it on the server if you have access to the file. Create a temp table bulk insert the file into it then join to the temp table for the query.

If the number of user name is large, then this is probably better than using an IN statement.

I Agree.

|||

This would make a good interview question. How would you filter a resultset from a list of IDs in a text file.

Whatever the first answer is then ask what you would do if the tool suggested wasn't available. If the file was a lot bigger than you thought, if it might have invalid data etc.

Dynamically assigning a value using the "Top N" Clause

I've developed a proc that takes an input parameter @.TopN Int. I want to
use it to dynamically pull the top n records from my DB as
such
Select TOP @.TopN UserID, Metric1, Metrics2...
Order By Metric1
I can only get this to work if I use an integer constant.
i.e. Select TOP 10 UserID
I Can't get it to work with the variable I'm passing in
Anyone know how to do this easily?
Thanks in advance
Message posted via http://www.webservertalk.comYou can use SET ROWCOUNT instead, but make sure that your ORDER BY
clause includes a key that is unique in the result set because SET
ROWCOUNT has no equivalent of the TOP WITH TIES option. Unless the
ORDER BY criteria is unique you may get unpredictable results.
David Portas
SQL Server MVP
--|||See:
http://groups.google.ca/groups?selm...FTNGP10.phx.gbl
Anith|||T Harris via webservertalk.com wrote:
> I've developed a proc that takes an input parameter @.TopN Int. I want
> to use it to dynamically pull the top n records from my DB as
> such
> Select TOP @.TopN UserID, Metric1, Metrics2...
> Order By Metric1
> I can only get this to work if I use an integer constant.
> i.e. Select TOP 10 UserID
> I Can't get it to work with the variable I'm passing in
> Anyone know how to do this easily?
> Thanks in advance
You'll need to wait for SQL 2005 for that.
David Gugick
Imceda Software
www.imceda.com|||Thanks, everyone for the responses.
Since the subset of data that I'm querying for is dynamic itself (i.e. I
don't know what the topN data set will be until after the order by is
applied) I can't directly apply the set rowcount. So the only way I can get
it to work it to query the dataset and then use the set rowcount against
the already ordered data set. This adds some overhead but it does work.
Tharrris
Message posted via http://www.webservertalk.com

Dynamic where clause with if..else or case

Hello all...

I am trying rewrite an sp that I have that is considered dynamic cause it builds a where clause based on a bunch of if statements then adds it to the the end of select

i.e

if...@.where = @.where + ' llll '

if...@.where = @.where + ' llll '

select @.statement = @.statement + @.where

exec(@.statement)

I have rewritten most of it to but I have several conditions that use ' contains' for the condition and I can't get SQL server to recognize an if statement or a case statement.

Is it possible to use either statement inside a where clause?

i.e

where if a = 1 then d=e

else contains(.....)

thanks

No. You cannot use control of flow statements in the WHERE clause. CASE is an expression so you can use it in the WHERE clause. Since CONTAINS is a predicate in itself, you need to do something like:

case ... when .. then (select 1 where contains()) else ... end = 1

But it is not clear why you need to use dynamic SQL or build WHERE clause like this. Using dynamic SQL can cause performance problems and has lot of security implications. So you need to be aware of the risks while using it. CONTAINS predicate in SQL Server 2000 (from SP3 I think) and SQL Server 2005 can take a variable for the search expression so you probably don't dynamic SQL for this. If you have different checks to perform then you may actually be better off using multiple IF statements that call specific SPs with SELECT statements. This will provide the best performance advantage.

|||

Thanks. I need the dynamic where based on search criteria because it was decided to make one SP compared to about 50 or more because there are possible combinations of the criteria...

The purpose of the contains is to search a text field for a value that is close to what what asked for. This is one of our slowest SPs so I thought I would give it a shot to make it quicker but I guess I will have to just be happy with what it is.

Thanks for the help.

|||

Check out the article at the link below for various techniques on how to do dynamic searches using SPs.

http://www.sommarskog.se/dyn-search.html

Dynamic WHERE Clause to Stored Procedure

Hi all!
I need to create a stored procedure with a parameter and then send a WHERE clause to that parameter (fields in the clause may vary from time to time thats why I want to make it as dynamic as possible) and use it in the query like (or something like) this:

----------------
@.crit varchar(100)

SELECT fldID, fldName FROM tblUsers
WHERE @.crit
----------------

Of course this does not work, but I don't know how it should be done, could someone please point me in the right direction on how to do this kind of queries.

cheers!
pelleU just pass a parameter @.crit into ur stored procedure and do the following with it inside:

EXEC('SELECT fldID, fldName FROM tblUsers WHERE' + @.crit );

This should help u... I hope.
Alex.|||Another example:

CREATE PROCEDURE [dbo].[Alter_Email_Users_Table]
(@.Column as nvarchar(50),
@.FieldType as nvarchar(50),
@.TableName as nvarchar(50),
@.Null as nvarchar(20),
@.Default as nvarchar(4000)
)
AS
Declare @.SQL nVarchar(4000)
Select @.SQL = 'ALTER TABLE ' + @.TableName + ' ADD ' + @.Column + ' ' + @.FieldType + ' ' + @.NULL + ' '
IF @.Default IS NOT NULL
SET @.SQL = @.SQL + ' Default ' + "'" + @.Default + "'"
exec (@.SQL)
GO

exec is the key part in both examples.

Dynamic Where clause in Stored Procedure

Hi, I have several parameters that I need to pass to stored procedure but sometimes some of them might be null. For example I might pass @.Path, @.Status, @.Role etc. depending on the user. Now I wonder if I should use dynamic Where clause or should I use some kind of switch, maybe case and hardcode my where clause. I first created several stored procedures like Documents_GetByRole, Documents_GetByRoleByStatus ... and now I want to combine them into one SP. Which approach is better. Thanks for your help.dynamic where clauses would eliminate most of the benefits of the stored procedure.

For every ad-hoc query that's executed, a new execution plan and compiliation takes place. Furthermore, your ram goes up.

A lot of databases will slow down with usage due to this. Therefore, hardcoding your where clauses is best. (like field = @.value)

Dynamic WHERE clause (SQL server 2005)

I'm in a situation where a user should be able to choose what data to
retrieve from a table. The criteria is not constant, sometime it is included
,
other times not. The problem I'm facing is how to create a dynamic WHERE
clause. I would prefere to avoid client side embedded SQL.
My first idea was to create a table valued functions that takes the criteria
and using dynamic SQL returns a table with the subset of data. The reason I
chose a TVF is so I can call it from multiple stored procedures.
The following function is accepted by SQL server, but when I run against it
I get the following error 'Only functions and extended stored procedures can
be executed from within a function.'
ALTER FUNCTION [dbo].[GetTagIds2]
(
@.TagMask NVARCHAR(50)
)
RETURNS @.ResultTable TABLE
(
TagId INT PRIMARY KEY NOT NULL,
Name NVARCHAR(100) NOT NULL
)
AS
BEGIN
DECLARE @.DynSql VARCHAR(1024);
-- this works fine
-- INSERT INTO @.ResultTable(TagId,Name)
-- SELECT IMS_Tag.TagId, IMS_Tag.Name FROM IMS_Tag
-- WHERE IMS_Tag.Name LIKE(@.TagMask)
SET @.DynSql = @.DynSql + 'INSERT INTO @.ResultTable(TagId,Name)';
SET @.DynSql = @.DynSql + 'SELECT IMS_Tag.TagId, IMS_Tag.Name FROM IMS_Tag';
SET @.DynSql = @.DynSql + 'WHERE IMS_Tag.Name LIKE(@.TagMask)';
EXEC sp_executesql @.DynSql;
RETURN
END
Any idea why this is not working?
If there are any better ways of doing this please let me know.You cannot use dynamic SQL in a function; you will need to use a different
approach.
http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/share_data.html
"Christopher Kimbell" <c_kimbell@.newsgroup.nospam> wrote in message
news:0BB5E543-76BC-4EC7-B038-DF0E9308D3DC@.microsoft.com...
> I'm in a situation where a user should be able to choose what data to
> retrieve from a table. The criteria is not constant, sometime it is
> included,
> other times not. The problem I'm facing is how to create a dynamic WHERE
> clause. I would prefere to avoid client side embedded SQL.
> My first idea was to create a table valued functions that takes the
> criteria
> and using dynamic SQL returns a table with the subset of data. The reason
> I
> chose a TVF is so I can call it from multiple stored procedures.
> The following function is accepted by SQL server, but when I run against
> it
> I get the following error 'Only functions and extended stored procedures
> can
> be executed from within a function.'
>
> ALTER FUNCTION [dbo].[GetTagIds2]
> (
> @.TagMask NVARCHAR(50)
> )
> RETURNS @.ResultTable TABLE
> (
> TagId INT PRIMARY KEY NOT NULL,
> Name NVARCHAR(100) NOT NULL
> )
> AS
> BEGIN
> DECLARE @.DynSql VARCHAR(1024);
> -- this works fine
> -- INSERT INTO @.ResultTable(TagId,Name)
> -- SELECT IMS_Tag.TagId, IMS_Tag.Name FROM IMS_Tag
> -- WHERE IMS_Tag.Name LIKE(@.TagMask)
> SET @.DynSql = @.DynSql + 'INSERT INTO @.ResultTable(TagId,Name)';
> SET @.DynSql = @.DynSql + 'SELECT IMS_Tag.TagId, IMS_Tag.Name FROM IMS_Tag';
> SET @.DynSql = @.DynSql + 'WHERE IMS_Tag.Name LIKE(@.TagMask)';
> EXEC sp_executesql @.DynSql;
> RETURN
> END
> Any idea why this is not working?
> If there are any better ways of doing this please let me know.|||Christopher Kimbell wrote:
> I'm in a situation where a user should be able to choose what data to
> retrieve from a table. The criteria is not constant, sometime it is
> included, other times not. The problem I'm facing is how to create a
> dynamic WHERE clause. I would prefere to avoid client side embedded
> SQL.
See Erland's article on this topic here:
http://www.sommarskog.se/index.html
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Thanks guys!
The information was very usefull.

dynamic where clause

Hi

I need some advice on which direction to take!

Consider this statement:

SELECT business_name FROM myTable WHERE town = @.town AND county = @.county

My problem is that i will not always have the @.county variable available. Is there a way to use an IF or a CASE inside the SQL statement (i know i can create two seperate sql statments but dont want to do it this way)? If it makes it easier, when the @.county variable is not available, it has a value of 0.

thanks again

Ps, i also know how to do it using dynamic sql using the EXEC() command, but i'd prefer to steer clear of this method also.SELECT business_name
FROM myTable
WHERE (town = @.town OR @.town IS NULL)
AND (county = @.county OR @.county = 0)
?|||Hi pootle

If the @.county has a value of 0 then i don't want to make it part of the WHERE clause, ie i dont want it to search the county column - in your example it would search all counties which are equal to 0?

This is what i'm trying to acheive, but only use one SELECT statment:

IF @.county <> '0'
BEGIN
SELECT business_name FROM myTable WHERE town = @.town AND county = @.county
END
ELSE
BEGIN
SELECT business_name FROM myTable WHERE town = @.town
END|||Hi Mattock

Did you try the code? I know the answer is no :) It does just what you ask for.|||lol, i'm real sorry, got the monday morning blues! Thick mode was well and truly stuck to 'ON'.

cheers again|||No probs :)

Dynamic Where clause

Hi
I have an array like this: myArray(n) ,and I need to make a dynamic WHERE
clause
e.g
Where Value = myArray(0) or myArray(1)...
But the where clause can not be explicit because the length of the array is
variable
What is a professional way to do something like this?
ThksAre you just using T-SQL or are you using VB in the front end?
"Kenny M." <KennyM@.discussions.microsoft.com> wrote in message
news:18A39651-A7B4-4208-9C52-515BC1198955@.microsoft.com...
> Hi
> I have an array like this: myArray(n) ,and I need to make a dynamic WHERE
> clause
> e.g
> Where Value = myArray(0) or myArray(1)...
> But the where clause can not be explicit because the length of the array
> is
> variable
> What is a professional way to do something like this?
> Thks
>|||Check out this article on ASPFAQ to get started:
http://www.aspfaq.com/show.asp?id=2248
"Kenny M." <KennyM@.discussions.microsoft.com> wrote in message
news:18A39651-A7B4-4208-9C52-515BC1198955@.microsoft.com...
> Hi
> I have an array like this: myArray(n) ,and I need to make a dynamic WHERE
> clause
> e.g
> Where Value = myArray(0) or myArray(1)...
> But the where clause can not be explicit because the length of the array
> is
> variable
> What is a professional way to do something like this?
> Thks
>|||>> What is a professional way to do something like this? <<
Put the values in the only data structure allowed in SQL, a table
WHERE x IN (SELECT x FROM Parmlist
)|||Yeah the code is in VB, is not transac but I would like to know how to make
it too.|||On the VB.NET side, create the SQL Statement dynamically using a String
variable and a For Loop:
Dim sqlstr As String
sqlstr = "SELECT * FROM table1 WHERE col1 IN ("
For i = 0 To MyArray.Length - 1
sqlstr += CType(MyArray(i), String)
If i < MyArray.Length - 1 Then
sqlstr += ","
End If
Next
sqlstr += ")"
"Kenny M." <KennyM@.discussions.microsoft.com> wrote in message
news:5897F0E6-FBE8-4BF4-97B4-DBD5A943DDB4@.microsoft.com...
> Yeah the code is in VB, is not transac but I would like to know how to
> make
> it too.|||pretty good thks
--
Kenny M.
"Michael C#" wrote:

> On the VB.NET side, create the SQL Statement dynamically using a String
> variable and a For Loop:
> Dim sqlstr As String
> sqlstr = "SELECT * FROM table1 WHERE col1 IN ("
> For i = 0 To MyArray.Length - 1
> sqlstr += CType(MyArray(i), String)
> If i < MyArray.Length - 1 Then
> sqlstr += ","
> End If
> Next
> sqlstr += ")"
>
> "Kenny M." <KennyM@.discussions.microsoft.com> wrote in message
> news:5897F0E6-FBE8-4BF4-97B4-DBD5A943DDB4@.microsoft.com...
>
>

Dynamic Where clause

What is the best way to dynamically choose the where clause based on a
variable.
In the following test example, depending on @.i value, WHERE clause could
compare against being null or not null.
Another way to do would be writing ugly sql string like @.select + @.where
Please let me know.
TIA...
set nocount on
go
create table z_test_del
(
c1 int,
c2 int
)
go
insert z_test_del values(1,null)
insert z_test_del values(2,333)
insert z_test_del values(3,null)
insert z_test_del values(4,5555)
go
declare @.i int
set @.i = 0
if (@.i = 0)
select * from z_test_del where c2 is null
else
select * from z_test_del where c2 is not null
go
drop table z_test_del
go>> What is the best way to dynamically choose the where clause based on a
variable. <<
Dynamic is poor choice of words in SQL -- it implies that you are
writing code on the fly.
SELECT *c1, c2 -- never use * in production code!!
FROM Foobar
WHERE (c2 IS NULL AND @.flag = 0)
OR (c2 IS NOT NULL AND @.flag <> 0);|||You can use a CASE or use OR-ed predicates or write two separate statements.
For some alternatives refer to: http://www.sommarskog.se/dyn-search.html
Anith|||Thanks Joe.
"--CELKO--" wrote:

> variable. <<
> Dynamic is poor choice of words in SQL -- it implies that you are
> writing code on the fly.
> SELECT *c1, c2 -- never use * in production code!!
> FROM Foobar
> WHERE (c2 IS NULL AND @.flag = 0)
> OR (c2 IS NOT NULL AND @.flag <> 0);
>

Dynamic WHERE clause

Hi
I have a WHERE clause, based on Params, passed in from a report, however, I
cannot get it to work properly.
I have to return two product codes, when a certain Types are passed in,
however, when it's anything else, I am only to return one product type.
Here's a sample of what I am trying to do:
WHERE
[Product Code] IN
(CASE
WHEN @.Type IN ('MD', 'BI') THEN
'CC', 'PO'
ELSE
'CC'
END)
Kind Regards
RickyTry
WHERE [Product Code] = 'CC' OR ( [Product Code] = 'PO' AND @.Type IN ('MD',
'BI') )
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"ricky" <ricky@.ricky.com> wrote in message
news:u$sWO8fjGHA.4716@.TK2MSFTNGP03.phx.gbl...
> Hi
> I have a WHERE clause, based on Params, passed in from a report, however,
> I
> cannot get it to work properly.
> I have to return two product codes, when a certain Types are passed in,
> however, when it's anything else, I am only to return one product type.
> Here's a sample of what I am trying to do:
>
> WHERE
> [Product Code] IN
> (CASE
> WHEN @.Type IN ('MD', 'BI') THEN
> 'CC', 'PO'
> ELSE
> 'CC'
> END)
>
> Kind Regards
> Ricky
>|||No, you can't do this. You can try something like this though...
WHERE
[Product Code] IN
(CASE
WHEN @.Type IN ('MD', 'BI') THEN
'PO'
ELSE
'CC'
END, 'CC')
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Hi chaps
Thank you both for the suggestion, couldn't work this out, been stuck for a
few hours. I thought it would be a simple thing to do...anyway, thanks
again.
Kind Regards
Ricky
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:06AFD45A-62AB-49EE-AFED-F6095D3C0C2C@.microsoft.com...
> No, you can't do this. You can try something like this though...
> WHERE
> [Product Code] IN
> (CASE
> WHEN @.Type IN ('MD', 'BI') THEN
> 'PO'
> ELSE
> 'CC'
> END, 'CC')
>
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>

Dynamic where clause

I am trying to write a stored procedure usp_select using dynamic sql to select from a table. The stored procedure will accept the where clause and/or the where clause parameters. I have tried 3 different methods -

Method 1 -

exec usp_select @.whereCondition='col1 like ''abc%'' and col2 = ''xyz'''

In usp_select, I'll build and execute the sql like -

set @.sql = N'select * from table ' + @.whereConition

exec sp_executesql @.sql

(basically @.sql becomes - select * from table where col1 like 'abc%' and col2 = 'xyz')

Method 2 -

exec usp_select @.whereCondition='col1 like @.p1 and col2 = @.p2', @.WhereParams='@.p1=abc%,@.p2=xyz'

In usp_select, I'll parse out the values in @.WhereParams and then build and execute the sql like -

set @.sql = N'declare @.p1 nvarchar(10),

@.p2 nvarchar(10);

set @.p1 = ''' + @.parsedValue1 + ''', @.p2 = ''' + @.parsedValue2 + '''; ' +

N'select col1 from table1 ' + @.whereCondition

exec (@.sql)

(basically @.sql becomes - declare @.p1 nvarchar(10), @.p2 nvarchar(10);

set @.pt = 'abc%', @.p2 = 'xyz';

select col1 from table1 where col1 like @.p1 and col2 = @.p2)

Method 3 -

similar to Method 2 but exec(@.sql) will be structured to become -

exec(declare @.vparam nvarchar(100), @.p1 nvarchar(10), @.p2 nvarchar(10);

set @.vparam='@.p1 nvarchar(10), @.p2 nvarchar(10)'

set @.p1 = 'abc%', @.p2 = 'xyz';

execute sp_executesql N''select col1 from table1 where col1 like @.p1 and col2 = @.p2', @.vparam, @.p, @.p2)

When I run sql profiler on the 3 methods, method 1 and 2 always result in a Cache Miss on the entire sql structure.

On method 3, a Cache Miss always occurs on the first part of the sql, ie, the first 3 lines where I declare and set the variables. Then a Cache Hit will happen on the execute sp_executesql part.

Do I have any performance gain using method 3 with both a Cache Miss and a Cache Hit?

I hope this is not too confusing. Because I do not know the where condition to the select procedure and hardcoding the values as in method 1 always results in a Cache Miss, therefore, I come up with the ideas in Method 2 and 3.

Any advice would be appreciated.

Yes. The Method-3 is recommanded to use.

The BOL says,

Because the actual text of the Transact-SQL statement in the sp_executesql string does not change between executions, the query optimizer will probably match the Transact-SQL statement in the second execution with the execution plan generated for the first execution. Therefore, SQL Server does not have to compile the second statement.

|||

What I don't understand is in Method2, the select part of @.sql is also static with parameters, why does it still result in a cache miss?

Why does sql profiler treat the entire @.sql string in Method2 as one sql statement but in Method3, it seems to treat it as two and result in both a cache miss and a cache hit? I am building @.sql the same way in both Method 2 and 3, the only difference is method 3 uses sp_executesql and the method 2 does not.

|||

LK,

1 - The optimizer could choose to not put the plan in cache, if it is sheap enough to compile it every time. Add event "SP:CacheInsert" to see if it is adding it.

2 - Even if it adds it, the batch is using variables in the expression on the "where" clause, so the query optimizer will not use the histogram (in case you have proper indexes for c1 and c2) from the index statistics to estimate cardinality, instead it will use the value of "All Density" associate with the group of columns. While using method 3, will use the histogram properly.

Statistics Used by the Query Optimizer in Microsoft SQL Server 2005

http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx

Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

AMB

|||

Thank you. The articles are quite helpful too.

Dynamic Where clause

Hello everyone,

I want to build a dynamic where clause which makes :

WHERE column1 = (@.parameter1 if @.parameter1 is not null) / (anything if @.parameter1 is null)

Basically I do not know how to set column1 = ANYTHING

Best regards and thanks.

Maybe something like

Code Snippet

Where @.parmameter1 is null

or @.parameter1 is not null and column1 = @.parameter1

Another alternative would be to use IF / ELSE around two distinct select statements rather than this particular WHERE clause. I suspect that this where syntax (and also the COALESCE syntax) will precipitate a SCAN instead of a seek. If your table is very small this won't matter.

You might be able to avoid the SCAN when you pass the parameter by using the IF / ELSE syntax PROVIDED that you have an index on column1.

|||

You can also use the coalesce function to determine which value to use. Here is an example using sys.databases

Code Snippet

DECLARE @.param1 NVARCHAR(20)

SET @.param1 = 'master'

-- SET @.param1 = NULL

SELECT * FROM sys.databases

WHERE name = COALESCE(@.param1, name)

Try it with both parameter settings. One will return just the row for master, the other will return all rows.

|||@. Kent Waldrop Thank you very much for this trick.. It seems simple but does a lot !

Dynamic Where clause

I need to build a dynamic where clause. Somehow I can't get it to work.
Here's the stored procedure. I believe I'm not concat. the
@.WhereOrderByClause parameter correct? Does anybody have any idea's?
Joshua
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- ========================================
=====
-- Author: JBlubaugh
-- Create date: 03/13/2006
-- Description: Gets information for Program
-- Summary report
-- ========================================
=====
ALTER PROCEDURE [cle].[getProgramSummaryRpt]
-- Add the parameters for the stored procedure here
@.ProgName varchar(300),
@.ProgNo int,
@.StartDate datetime,
@.EndDate datetime,
@.ProgCatCode int,
@.ProgTypeName varchar(30),
@.OfficeCode varchar(3),
@.DateCreated datetime,
@.SortOrder varchar(10),
@.WhereOrderByClause varchar(500)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
if @.ProgName IS NOT NULL
set @.WhereOrderByClause = ' WHERE p.ProgName IN (' + @.ProgName + ')'
if @.ProgNo IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgNo IN (' +
@.ProgNo + ')'
if @.StartDate IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.StartDate >= ' +
@.StartDate
if @.EndDate IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.EndDate <= ' +
@.EndDate
if @.ProgCatCode IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgCatCode IN ('
+ @.ProgCatCode + ')'
if @.ProgTypeName IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND t.ProgTypeName IN
(' + @.ProgTypeName + ')'
if @.OfficeCode IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND o.OfficeCode IN (' +
@.OfficeCode + ')'
if @.DateCreated IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' p.CreatedOn >= ' +
@.DateCreated
if @.SortOrder = 'p.ProgNo'
set @.WhereOrderByClause = @.WhereOrderByClause + ' Order By p.ProgNo ASC'
else
set @.WhereOrderByClause = @.WhereOrderByClause + ' Order By p.CreatedOn ASC'
-- Insert statements for procedure here
SELECT p.ProgNo, p.StartDate, p.EndDate, p.ProgName,
p.CreatedBy, p.CreatedOn, p.LocationCode, p.ProgCatCode,
t.ProgTypeName, l.LocDesc, c.ProgCatName, o.OfficeCode
FROM Programs p
LEFT OUTER JOIN ProgramLocations l
ON p.LocationCode = l.LocCode
LEFT OUTER JOIN ProgramCats c
ON p.ProgCatCode = c.ProgCatCode
LEFT OUTER JOIN ProgOffices o
ON p.ProgNo = o.ProgNo
LEFT OUTER JOIN ProgramTypes t
ON p.ProgTypeCode = t.ProgTypeCode
& @.WhereOrderByClause
END> if @.ProgName IS NOT NULL
> set @.WhereOrderByClause = ' WHERE p.ProgName IN (' + @.ProgName + ')'
Eep.
http://www.sommarskog.se/dyn-search.html
http://www.sommarskog.se/dynamic_sql.html|||You need to use EXEC or sp_executesql to run dynamic SQL. You'll have to
store the exec string in a variable and then execute it:
DECLARE @.str varchar (8000)
set @.str = 'SELECT p.ProgNo, p.StartDate, p.EndDate, p.ProgName,
p.CreatedBy, p.CreatedOn, p.LocationCode, p.ProgCatCode,
t.ProgTypeName, l.LocDesc, c.ProgCatName, o.OfficeCode
FROM Programs p
LEFT OUTER JOIN ProgramLocations l
ON p.LocationCode = l.LocCode
LEFT OUTER JOIN ProgramCats c
ON p.ProgCatCode = c.ProgCatCode
LEFT OUTER JOIN ProgOffices o
ON p.ProgNo = o.ProgNo
LEFT OUTER JOIN ProgramTypes t
ON p.ProgTypeCode = t.ProgTypeCode'
& @.WhereOrderByClause
EXEC (@.str)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"gdjoshua" <gdjoshua@.discussions.microsoft.com> wrote in message
news:2B4A1C0E-6D0F-4EAF-809C-5CAAB2549E48@.microsoft.com...
I need to build a dynamic where clause. Somehow I can't get it to work.
Here's the stored procedure. I believe I'm not concat. the
@.WhereOrderByClause parameter correct? Does anybody have any idea's?
Joshua
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- ========================================
=====
-- Author: JBlubaugh
-- Create date: 03/13/2006
-- Description: Gets information for Program
-- Summary report
-- ========================================
=====
ALTER PROCEDURE [cle].[getProgramSummaryRpt]
-- Add the parameters for the stored procedure here
@.ProgName varchar(300),
@.ProgNo int,
@.StartDate datetime,
@.EndDate datetime,
@.ProgCatCode int,
@.ProgTypeName varchar(30),
@.OfficeCode varchar(3),
@.DateCreated datetime,
@.SortOrder varchar(10),
@.WhereOrderByClause varchar(500)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
if @.ProgName IS NOT NULL
set @.WhereOrderByClause = ' WHERE p.ProgName IN (' + @.ProgName + ')'
if @.ProgNo IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgNo IN (' +
@.ProgNo + ')'
if @.StartDate IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.StartDate >= ' +
@.StartDate
if @.EndDate IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.EndDate <= ' +
@.EndDate
if @.ProgCatCode IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgCatCode IN ('
+ @.ProgCatCode + ')'
if @.ProgTypeName IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND t.ProgTypeName IN
(' + @.ProgTypeName + ')'
if @.OfficeCode IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND o.OfficeCode IN (' +
@.OfficeCode + ')'
if @.DateCreated IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' p.CreatedOn >= ' +
@.DateCreated
if @.SortOrder = 'p.ProgNo'
set @.WhereOrderByClause = @.WhereOrderByClause + ' Order By p.ProgNo ASC'
else
set @.WhereOrderByClause = @.WhereOrderByClause + ' Order By p.CreatedOn ASC'
-- Insert statements for procedure here
SELECT p.ProgNo, p.StartDate, p.EndDate, p.ProgName,
p.CreatedBy, p.CreatedOn, p.LocationCode, p.ProgCatCode,
t.ProgTypeName, l.LocDesc, c.ProgCatName, o.OfficeCode
FROM Programs p
LEFT OUTER JOIN ProgramLocations l
ON p.LocationCode = l.LocCode
LEFT OUTER JOIN ProgramCats c
ON p.ProgCatCode = c.ProgCatCode
LEFT OUTER JOIN ProgOffices o
ON p.ProgNo = o.ProgNo
LEFT OUTER JOIN ProgramTypes t
ON p.ProgTypeCode = t.ProgTypeCode
& @.WhereOrderByClause
END|||Tom,
I get this error message'?
Msg 403, Level 16, State 1, Procedure getProgramSummaryRpt, Line 62
Invalid operator for data type. Operator equals boolean AND, type equals
varchar.
Joshua
"Tom Moreau" wrote:

> You need to use EXEC or sp_executesql to run dynamic SQL. You'll have to
> store the exec string in a variable and then execute it:
> DECLARE @.str varchar (8000)
> set @.str = 'SELECT p.ProgNo, p.StartDate, p.EndDate, p.ProgName,
> p.CreatedBy, p.CreatedOn, p.LocationCode, p.ProgCatCode,
> t.ProgTypeName, l.LocDesc, c.ProgCatName, o.OfficeCode
> FROM Programs p
> LEFT OUTER JOIN ProgramLocations l
> ON p.LocationCode = l.LocCode
> LEFT OUTER JOIN ProgramCats c
> ON p.ProgCatCode = c.ProgCatCode
> LEFT OUTER JOIN ProgOffices o
> ON p.ProgNo = o.ProgNo
> LEFT OUTER JOIN ProgramTypes t
> ON p.ProgTypeCode = t.ProgTypeCode'
> & @.WhereOrderByClause
> EXEC (@.str)
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "gdjoshua" <gdjoshua@.discussions.microsoft.com> wrote in message
> news:2B4A1C0E-6D0F-4EAF-809C-5CAAB2549E48@.microsoft.com...
> I need to build a dynamic where clause. Somehow I can't get it to work.
> Here's the stored procedure. I believe I'm not concat. the
> @.WhereOrderByClause parameter correct? Does anybody have any idea's?
> Joshua
>
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> go
>
> -- ========================================
=====
> -- Author: JBlubaugh
> -- Create date: 03/13/2006
> -- Description: Gets information for Program
> -- Summary report
> -- ========================================
=====
> ALTER PROCEDURE [cle].[getProgramSummaryRpt]
> -- Add the parameters for the stored procedure here
> @.ProgName varchar(300),
> @.ProgNo int,
> @.StartDate datetime,
> @.EndDate datetime,
> @.ProgCatCode int,
> @.ProgTypeName varchar(30),
> @.OfficeCode varchar(3),
> @.DateCreated datetime,
> @.SortOrder varchar(10),
> @.WhereOrderByClause varchar(500)
> AS
> BEGIN
> -- SET NOCOUNT ON added to prevent extra result sets from
> -- interfering with SELECT statements.
> SET NOCOUNT ON;
> if @.ProgName IS NOT NULL
> set @.WhereOrderByClause = ' WHERE p.ProgName IN (' + @.ProgName + ')'
> if @.ProgNo IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgNo IN (' +
> @.ProgNo + ')'
> if @.StartDate IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.StartDate >= ' +
> @.StartDate
> if @.EndDate IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.EndDate <= ' +
> @.EndDate
> if @.ProgCatCode IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgCatCode IN ('
> + @.ProgCatCode + ')'
> if @.ProgTypeName IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND t.ProgTypeName IN
> (' + @.ProgTypeName + ')'
> if @.OfficeCode IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND o.OfficeCode IN (' +
> @.OfficeCode + ')'
> if @.DateCreated IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' p.CreatedOn >= ' +
> @.DateCreated
> if @.SortOrder = 'p.ProgNo'
> set @.WhereOrderByClause = @.WhereOrderByClause + ' Order By p.ProgNo ASC'
> else
> set @.WhereOrderByClause = @.WhereOrderByClause + ' Order By p.CreatedOn ASC
'
> -- Insert statements for procedure here
> SELECT p.ProgNo, p.StartDate, p.EndDate, p.ProgName,
> p.CreatedBy, p.CreatedOn, p.LocationCode, p.ProgCatCode,
> t.ProgTypeName, l.LocDesc, c.ProgCatName, o.OfficeCode
> FROM Programs p
> LEFT OUTER JOIN ProgramLocations l
> ON p.LocationCode = l.LocCode
> LEFT OUTER JOIN ProgramCats c
> ON p.ProgCatCode = c.ProgCatCode
> LEFT OUTER JOIN ProgOffices o
> ON p.ProgNo = o.ProgNo
> LEFT OUTER JOIN ProgramTypes t
> ON p.ProgTypeCode = t.ProgTypeCode
> & @.WhereOrderByClause
> END
>
>|||Try something like this in case because the first one may not exist
set @.WhereOrderByClause = ' WHERE 1=1'
if @.ProgName IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgName IN ('
+ @.ProgName + ')'
if @.ProgNo IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgNo IN (' +
@.ProgNo + ')'|||I've already fixed the other part... this is what i'm trying:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- ========================================
=====
-- Author: JBlubaugh
-- Create date: 03/13/2006
-- Description: Gets information for Program
-- Summary report
-- ========================================
=====
ALTER PROCEDURE [cle].[getProgramSummaryRpt]
-- Add the parameters for the stored procedure here
@.ProgName varchar(300),
@.ProgNo int,
@.StartDate datetime,
@.EndDate datetime,
@.ProgCatCode int,
@.ProgTypeName varchar(30),
@.OfficeCode varchar(3),
@.DateCreated datetime,
@.SortOrder varchar(10),
@.WhereOrderByClause varchar(500)
AS
BEGIN
Declare @.str varchar (8000)
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
set @.WhereOrderByClause = ' WHERE '
if @.ProgName IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + 'p.ProgName IN (' +
@.ProgName + ')'
if @.ProgNo IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgNo IN (' +
@.ProgNo + ')'
if @.StartDate IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.StartDate >= ' +
@.StartDate
if @.EndDate IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.EndDate <= ' +
@.EndDate
if @.ProgCatCode IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgCatCode IN ('
+ @.ProgCatCode + ')'
if @.ProgTypeName IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND t.ProgTypeName IN
(' + @.ProgTypeName + ')'
if @.OfficeCode IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND o.OfficeCode IN (' +
@.OfficeCode + ')'
if @.DateCreated IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' p.CreatedOn >= ' +
@.DateCreated
if @.SortOrder = 'p.ProgNo'
set @.WhereOrderByClause = @.WhereOrderByClause + ' Order By p.ProgNo ASC'
else
set @.WhereOrderByClause = @.WhereOrderByClause + ' Order By p.CreatedOn ASC'
set @.str = 'SELECT p.ProgNo, p.StartDate, p.EndDate, p.ProgName,
p.CreatedBy, p.CreatedOn, p.LocationCode, p.ProgCatCode,
t.ProgTypeName, l.LocDesc, c.ProgCatName, o.OfficeCode
FROM Programs p
LEFT OUTER JOIN ProgramLocations l
ON p.LocationCode = l.LocCode
LEFT OUTER JOIN ProgramCats c
ON p.ProgCatCode = c.ProgCatCode
LEFT OUTER JOIN ProgOffices o
ON p.ProgNo = o.ProgNo
LEFT OUTER JOIN ProgramTypes t
ON p.ProgTypeCode = t.ProgTypeCode'
& @.WhereOrderByClause
-- Insert statements for procedure here
EXEC (@.str)
END
"JeffB" wrote:

> Try something like this in case because the first one may not exist
> set @.WhereOrderByClause = ' WHERE 1=1'
> if @.ProgName IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgName IN ('
> + @.ProgName + ')'
> if @.ProgNo IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgNo IN (' +
> @.ProgNo + ')'
>|||& is not for concatenation (someone's been playing with VB/VBScript). You
should use + instead of &
"gdjoshua" <gdjoshua@.discussions.microsoft.com> wrote in message
news:363E0952-AC3F-44FF-959E-B822DFAD7EEB@.microsoft.com...
> Tom,
> I get this error message'?
> Msg 403, Level 16, State 1, Procedure getProgramSummaryRpt, Line 62
> Invalid operator for data type. Operator equals boolean AND, type equals
> varchar.|||What if @.ProgName is NULL and @.ProgNo is 3? Then the sql generated
will be 'WHERE AND p.ProgNo IN (3)' which won't work. The initial
setting should be 'WHERE 1 = 1
set @.WhereOrderByClause = ' WHERE '
if @.ProgName IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause +
'p.ProgName IN (' +
@.ProgName + ')'
if @.ProgNo IS NOT NULL
set @.WhereOrderByClause = @.WhereOrderByClause + ' AND
p.ProgNo IN (' +
@.ProgNo + ')'|||Doh! I do that a lot - jumping back and forth between VB and T-SQL. :-S
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eReETs6RGHA.1948@.TK2MSFTNGP09.phx.gbl...
& is not for concatenation (someone's been playing with VB/VBScript). You
should use + instead of &
"gdjoshua" <gdjoshua@.discussions.microsoft.com> wrote in message
news:363E0952-AC3F-44FF-959E-B822DFAD7EEB@.microsoft.com...
> Tom,
> I get this error message'?
> Msg 403, Level 16, State 1, Procedure getProgramSummaryRpt, Line 62
> Invalid operator for data type. Operator equals boolean AND, type equals
> varchar.|||Use = instead of in, unless you are actually dealing with a list of values
contained within a string. It works either way, but is easier to understand
with the =.
When concatenating the string together, you need to place quotes around your
values, or pass them explicitly as parameters with sp_executesql. Two
single quotes are used to represent single quotes within quotes.
i.e.
set @.ProgName = 'Test'
set @.WhereOrderByClause = ' WHERE p.ProgName = ''' + @.ProgName + ''''
the resulting strign is
WHERE p.ProgName = 'Test'
or, pass the parameters to the sp_executesql:
set @.ProgName = 'Test'
set @.WhereOrderByClause = ' WHERE p.ProgName = @.ProgName'
set @.SelectString = 'Select * from SomeTable ' + @.WhereOrderByClause
SET @.ParmDefinition = '@.ProgName varchar(300)'
/* Execute the string with the first parameter value. */
EXECUTE sp_executesql @.SelectString, @.ParmDefinition, @.ProgName =
@.ProgName
"gdjoshua" <gdjoshua@.discussions.microsoft.com> wrote in message
news:2B4A1C0E-6D0F-4EAF-809C-5CAAB2549E48@.microsoft.com...
> I need to build a dynamic where clause. Somehow I can't get it to work.
> Here's the stored procedure. I believe I'm not concat. the
> @.WhereOrderByClause parameter correct? Does anybody have any idea's?
> Joshua
>
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> go
>
> -- ========================================
=====
> -- Author: JBlubaugh
> -- Create date: 03/13/2006
> -- Description: Gets information for Program
> -- Summary report
> -- ========================================
=====
> ALTER PROCEDURE [cle].[getProgramSummaryRpt]
> -- Add the parameters for the stored procedure here
> @.ProgName varchar(300),
> @.ProgNo int,
> @.StartDate datetime,
> @.EndDate datetime,
> @.ProgCatCode int,
> @.ProgTypeName varchar(30),
> @.OfficeCode varchar(3),
> @.DateCreated datetime,
> @.SortOrder varchar(10),
> @.WhereOrderByClause varchar(500)
> AS
> BEGIN
> -- SET NOCOUNT ON added to prevent extra result sets from
> -- interfering with SELECT statements.
> SET NOCOUNT ON;
> if @.ProgName IS NOT NULL
> set @.WhereOrderByClause = ' WHERE p.ProgName IN (' + @.ProgName + ')'
> if @.ProgNo IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgNo IN (' +
> @.ProgNo + ')'
> if @.StartDate IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.StartDate >= ' +
> @.StartDate
> if @.EndDate IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.EndDate <= ' +
> @.EndDate
> if @.ProgCatCode IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND p.ProgCatCode IN ('
> + @.ProgCatCode + ')'
> if @.ProgTypeName IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND t.ProgTypeName IN
> (' + @.ProgTypeName + ')'
> if @.OfficeCode IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' AND o.OfficeCode IN (' +
> @.OfficeCode + ')'
> if @.DateCreated IS NOT NULL
> set @.WhereOrderByClause = @.WhereOrderByClause + ' p.CreatedOn >= ' +
> @.DateCreated
> if @.SortOrder = 'p.ProgNo'
> set @.WhereOrderByClause = @.WhereOrderByClause + ' Order By p.ProgNo ASC'
> else
> set @.WhereOrderByClause = @.WhereOrderByClause + ' Order By p.CreatedOn
ASC'
> -- Insert statements for procedure here
> SELECT p.ProgNo, p.StartDate, p.EndDate, p.ProgName,
> p.CreatedBy, p.CreatedOn, p.LocationCode, p.ProgCatCode,
> t.ProgTypeName, l.LocDesc, c.ProgCatName, o.OfficeCode
> FROM Programs p
> LEFT OUTER JOIN ProgramLocations l
> ON p.LocationCode = l.LocCode
> LEFT OUTER JOIN ProgramCats c
> ON p.ProgCatCode = c.ProgCatCode
> LEFT OUTER JOIN ProgOffices o
> ON p.ProgNo = o.ProgNo
> LEFT OUTER JOIN ProgramTypes t
> ON p.ProgTypeCode = t.ProgTypeCode
> & @.WhereOrderByClause
> END
>

Dynamic WHERE Clause

Hi
I have a query which has a few different Time Period columns:
Half_Year (H1,H2)
Quarters (Q1,Q2,Q3,Q4)
Months (M1,M2,M3,... M12)
These periods are held in three difference columns.
I need to run this query with 2 params. One will be the year and other will
be one of the above three:
i.e
sp_Rating 2005, 'H1'
This is all transactions in months 1=6 for the year 2005.
or sp_Rating 2005, 'Q3' or sp_Rating 2005, 'M7'
How can I dynamically interrogate the correct column, based on the param
supplied (H, Q, M) ?
Kind Regards
Ricky
(WIN2K,SQL2K-SP4)Ricky (ricky@.msn.com) writes:
> I have a query which has a few different Time Period columns:
> Half_Year (H1,H2)
> Quarters (Q1,Q2,Q3,Q4)
> Months (M1,M2,M3,... M12)
> These periods are held in three difference columns.
> I need to run this query with 2 params. One will be the year and other
> will be one of the above three:
> i.e
> sp_Rating 2005, 'H1'
> This is all transactions in months 1=6 for the year 2005.
> or sp_Rating 2005, 'Q3' or sp_Rating 2005, 'M7'
> How can I dynamically interrogate the correct column, based on the param
> supplied (H, Q, M) ?
First of all, don't call your procedures sp_something. That prefix is
reserved for system procedures, and SQL Server first looks for a
procedure with such a name in the master database.
As for the question, I'm afraid that I dno't really understand. Does
this table has three columns? May then I ask the stupid question if
H1 can appear together with M7 to M12? I realise that if periodisation
takes place, this can happen. Then again, could a row really have
H1, Q3 and M10?
If you need all three columns try this:
SELECT ...
FROM tbl
WHERE (Halt_year = @.period AND @.period LIKE 'H%')
OR (Quarter = @.period AND @.period LIKE 'Q%')
OR (Months = @.period AND @.period LIKE 'M%')
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|||>> I have a query which has a few different Time Period columns: <<
Really? Mind showing us? Please post DDL, so that people do not have
to guess what the keys, constraints, Declarative Referential Integrity,
data types, etc. in your schema are. Sample data is also a good idea,
along with clear specifications. It is very hard to debug code when
you do not let us see it.
Why? Are they LOGICALLY DIFFERENT? There is usually only one kind of
time in the universe. Try a proper design:
CREATE TABLE PeriodCalendar
(period_name CHAR(15) NOT NULL PRIMARY KEY
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL);
INSERT INTO PeriodCalendar ('Q1-2006', '2006-01-01', '2006-03-341');
etc. for all the possible fiscal, marketing and calendaral periods you
use.
Again, not a good design; you seem to think that a year should be an
integer, while the ANSI standard say it is a CHAR(4); get a copy of the
8601 standards, too. Now life a JOIN and a BETWEEN predicate.
And you have already been told about not using "sp_" prefixes because
they refer to where something is phycially located as well as having a
special meaning in SQL Server.|||Thanks guys for the reply, I'm ashamed to say that I have been using the
'sp_' prefix, I did no know that this was the case, I shall rename mine to
something else today/tomorrow.
Kind Regards
Ricky
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1149463964.082284.170110@.i39g2000cwa.googlegroups.com...
> Really? Mind showing us? Please post DDL, so that people do not have
> to guess what the keys, constraints, Declarative Referential Integrity,
> data types, etc. in your schema are. Sample data is also a good idea,
> along with clear specifications. It is very hard to debug code when
> you do not let us see it.
>
> Why? Are they LOGICALLY DIFFERENT? There is usually only one kind of
> time in the universe. Try a proper design:
> CREATE TABLE PeriodCalendar
> (period_name CHAR(15) NOT NULL PRIMARY KEY
> start_date DATETIME NOT NULL,
> end_date DATETIME NOT NULL);
> INSERT INTO PeriodCalendar ('Q1-2006', '2006-01-01', '2006-03-341');
> etc. for all the possible fiscal, marketing and calendaral periods you
> use.
>
other will be one of the above three: sp_Rating 2005, 'H1' <<
> Again, not a good design; you seem to think that a year should be an
> integer, while the ANSI standard say it is a CHAR(4); get a copy of the
> 8601 standards, too. Now life a JOIN and a BETWEEN predicate.
> And you have already been told about not using "sp_" prefixes because
> they refer to where something is phycially located as well as having a
> special meaning in SQL Server.
>|||That's a mistake that we all make, until someone points out to us the
dangers.
In addition to what --CELKO-- posted, check out this article on calendar
tables, which goes into much more detail.
http://www.aspfaq.com/show.asp?id=2519
"ricky" <ricky@.ricky.com> wrote in message
news:O5wFxWHiGHA.4044@.TK2MSFTNGP03.phx.gbl...
> Thanks guys for the reply, I'm ashamed to say that I have been using the
> 'sp_' prefix, I did no know that this was the case, I shall rename mine to
> something else today/tomorrow.
> Kind Regards
> Ricky
>
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1149463964.082284.170110@.i39g2000cwa.googlegroups.com...
> other will be one of the above three: sp_Rating 2005, 'H1' <<
>|||Joe,
Just curious, what is the reason for making year a varchar instead of an
integer (in the standard)?
I have always thought of it as an integer that one might add and subtract
from for various date functions (of course leap year can complicate it).
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1149463964.082284.170110@.i39g2000cwa.googlegroups.com...
> Again, not a good design; you seem to think that a year should be an
> integer, while the ANSI standard say it is a CHAR(4); get a copy of the
> 8601 standards, too. Now life a JOIN and a BETWEEN predicate.|||
--CELKO-- wrote:

>Again, not a good design; you seem to think that a year should be an
>integer, while the ANSI standard say it is a CHAR(4); get a copy of the
>8601 standards, too. Now life a JOIN and a BETWEEN predicate.
>
ANSI 8601 is a standard for the "representation" of dates and times.
It's not a standard for how to store them. If you think the ANSI standard
says that a year *is* a char(4), please quote the relevant section of the
standard.
My copy of the standard says only that "In *expressions* of calendar
dates, year is generally *represented* by four digits..." [emphasis mine]
Steve Kass
Drew University
http://www.stevekass.com|||>> Just curious, what is the reason for making year a varchar instead of an
integer (in the standard)? <<
Mostly history. We based the SQL Standard on the pre-existing the
ISO-8601 Standards which is for display; it says nothing about internal
storage. We wanted to avoid anything to do with internal storage, like
specifying the use of numbers for dates.
You can see this with the specs for "EXTRACT(<temporal unit> FROM
<temporal exp)>" and the fact you have to use strings with "INTERVAL
<exp> <temporal unit>"; that is "INTERVAL '12' YEAR" works and
"INTERVAL 12 YEAR" is an error. What newbies will do is use integers
and force a casting in certain places.
Of course, by the time we got something into SQL, each vendor had a
proprietary library and storage method which was exposed to the users.
Rats!!|||
--CELKO-- wrote:

>Mostly history. We based the SQL Standard on the pre-existing the
>ISO-8601 Standards which is for display; it says nothing about internal
>storage. We wanted to avoid anything to do with internal storage, like
>specifying the use of numbers for dates.
>You can see this with the specs for "EXTRACT(<temporal unit> FROM
><temporal exp)>" and the fact you have to use strings with "INTERVAL
><exp> <temporal unit>"; that is "INTERVAL '12' YEAR" works and
>"INTERVAL 12 YEAR" is an error. What newbies will do is use integers
>and force a casting in certain places.
>
The reason the <interval string> is a string is that its domain is not
<integer>. The domain includes '1-4', for example.
This has nothing whatsoever to do with the issue of whether ANSI 8601
says that
a "year is a char(4)" (it does not).
SK

>Of course, by the time we got something into SQL, each vendor had a
>proprietary library and storage method which was exposed to the users.
> Rats!!
>
>|||I apologise if I have caused a disagreement, but is there an issue, if I do
format YEAR as an INT?
"Steve Kass" <skass@.drew.edu> wrote in message
news:uZknxeMiGHA.4080@.TK2MSFTNGP03.phx.gbl...
>
> --CELKO-- wrote:
>
an integer (in the standard)? <<
> The reason the <interval string> is a string is that its domain is not
> <integer>. The domain includes '1-4', for example.
> This has nothing whatsoever to do with the issue of whether ANSI 8601
> says that
> a "year is a char(4)" (it does not).
> SK
>

dynamic table name in from clause

Hello All,
I am trying to create UDF that will take in tablename and columnname,
maxlength as parameters. Based on the tablename and columnname, I want to
return the length of the longest columndata. If the length value is bigger
than the maxlength parameter, I pass in , I just want to return the
maxlength.
Basically, I am trying to do the following:
alter FUNCTION dbo.rp_MaxColumnLength
(@.TableName varchar(200),@.ColumnName varchar(200),@.MaxLenth INT)
RETURNS INT
AS
BEGIN
DECLARE @.ColMaxLength INT
SELECT @.ColMaxLength = MAX(LEN(@.ColumnName)) FROM @.TableName
if @.colmaxlegth > @.Maxlength
return @.MaxLength
else
return @.colmaxlength
END
But I guess, I cannot use a variable in the FROM clause as a tablename.
Does anyone know a workaround?
Please help.
Thanks, sqlgirlYou might want to start with the following article:
http://www.sommarskog.se/dynamic_sql.html
It has some relevant details and implications of using such approaches.
Anith|||
Hey Amith,
Thanks a bunch. I was able to solve my problem by looking at the
article.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!