Dynamic Query!

I am trying to create a stored procedure containing a dynamic query.
I am still new to using conditionals in sql, so any help to
get this query running would be appreciated!!!
CREATE PROCEDURE [dbo].[get_emp_list]
@.name VARCHAR(60) = NULL,
@.from_dt SMALLDATETIME = NULL,
@.to_dt SMALLDATETIME = NULL
AS
BEGIN
SELECT
employee_id, first_name, last_name,
company_rep, user_nme, user_pass
FROM
employee
WHERE
IF @.name IS NOT NULL
first_name + ' ' + last_name LIKE @.name
IF @.name IS NOT NULL AND @.from_dt IS NOT NULL
AND start_dt >= from_dt AND <= to_dt
ELSE
start_dt >= from_dt AND <= to_dt
ENDHi
Just check if this solves the purpose
CREATE PROCEDURE [dbo].[get_emp_list]
@.name VARCHAR(60) = NULL,
@.from_dt SMALLDATETIME = NULL,
@.to_dt SMALLDATETIME = NULL
AS
BEGIN
SELECT
employee_id, first_name, last_name,
company_rep, user_nme, user_pass
FROM
employee
WHERE
CASE WHEN @.name IS NOT NULL
first_name + ' ' + last_name LIKE @.name
CASE WHEN @.name IS NOT NULL AND @.from_dt IS NOT NULL
start_dt >= from_dt AND <= to_dt
ELSE
start_dt >= from_dt AND <= to_dt
END
END
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"AJ" wrote:

> I am trying to create a stored procedure containing a dynamic query.
> I am still new to using conditionals in sql, so any help to
> get this query running would be appreciated!!!
> CREATE PROCEDURE [dbo].[get_emp_list]
> @.name VARCHAR(60) = NULL,
> @.from_dt SMALLDATETIME = NULL,
> @.to_dt SMALLDATETIME = NULL
> AS
> BEGIN
> SELECT
> employee_id, first_name, last_name,
> company_rep, user_nme, user_pass
> FROM
> employee
> WHERE
> IF @.name IS NOT NULL
> first_name + ' ' + last_name LIKE @.name
> IF @.name IS NOT NULL AND @.from_dt IS NOT NULL
> AND start_dt >= from_dt AND <= to_dt
> ELSE
> start_dt >= from_dt AND <= to_dt
> END|||Well you could approach it very simplistically and just replace all your
different IF cases with OR operations (because that's what they really
are) like this:
SELECT
employee_id, first_name, last_name,
company_rep, user_nme, user_pass
FROM
employee
WHERE (@.name IS NOT NULL and first_name + ' ' + last_name LIKE @.name)
OR (@.name IS NOT NULL AND @.from_dt IS NOT NULL AND start_dt BETWEEN
@.from_dt AND @.to_dt)
OR (start_dt BETWEEN @.from_dt AND @.to_dt)
You could shuffle the WHERE clause around a bit but chances are the
query optimiser will come up with the same plan for the majority of the
variations so you may as well stick to something that you understand and
that's readable (so those who maintain the system after you aren't
bamboozled by your code).
I changed your "<= AND >=" bits to "BETWEEN" because it's a little more
readable IMHO. Also I noticed you left off the '@.' symbol on a couple
references to your proc parameters in the WHERE clause (to_dt & from_dt).
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
AJ wrote:

>I am trying to create a stored procedure containing a dynamic query.
>I am still new to using conditionals in sql, so any help to
>get this query running would be appreciated!!!
>CREATE PROCEDURE [dbo].[get_emp_list]
> @.name VARCHAR(60) = NULL,
> @.from_dt SMALLDATETIME = NULL,
> @.to_dt SMALLDATETIME = NULL
>AS
>BEGIN
> SELECT
> employee_id, first_name, last_name,
> company_rep, user_nme, user_pass
> FROM
> employee
> WHERE
> IF @.name IS NOT NULL
> first_name + ' ' + last_name LIKE @.name
> IF @.name IS NOT NULL AND @.from_dt IS NOT NULL
> AND start_dt >= from_dt AND <= to_dt
> ELSE
> start_dt >= from_dt AND <= to_dt
>END
>|||Hmmm... The CASE statement is wrong. I think you mean
WHERE
CASE
WHEN @.name IS NOT NULL
THEN first_name + ' ' + last_name LIKE @.name
WHEN @.name IS NOT NULL AND @.from_dt IS NOT NULL
THEN start_dt >= @.from_dt AND start_dt <= @.to_dt
ELSE
start_dt >= @.from_dt AND start_dt <= @.to_dt
END
But I'm not sure that would work even. BOL says the bit after THEN can be a
ny valid SQL expression but I don't know if "x LIKE y" or "a >= x and a <= b
" are valid in this context (even though they just resolve to a boolean, whi
ch I guess is a valid SQL e
xpression) - never tried that before. At the very least it's a little unort
hodox. Typically a CASE is used to return a specific value to compare somet
hing to like this
WHERE MyCol = (CASE
WHEN a THEN SomeVal
WHEN b THEN SomeOtherVal
ELSE SomeCatchAllVal
END)
Also, the middle case is redundant because it's the same result as the ELSE
case. You could simply write it as
WHERE
CASE
WHEN @.name IS NOT NULL
THEN first_name + ' ' + last_name LIKE @.name
ELSE
start_dt >= @.from_dt AND start_dt <= @.to_dt
END
However, you could get rid of the CASE statement entirely by saying
WHERE first_name + ' ' + last_name LIKE @.name
OR start_dt BETWEEN @.from_dt AND @.to_dt
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Chandra wrote:

>Hi
>Just check if this solves the purpose
>CREATE PROCEDURE [dbo].[get_emp_list]
> @.name VARCHAR(60) = NULL,
> @.from_dt SMALLDATETIME = NULL,
> @.to_dt SMALLDATETIME = NULL
>AS
>BEGIN
> SELECT
> employee_id, first_name, last_name,
> company_rep, user_nme, user_pass
> FROM
> employee
> WHERE
> CASE WHEN @.name IS NOT NULL
> first_name + ' ' + last_name LIKE @.name
> CASE WHEN @.name IS NOT NULL AND @.from_dt IS NOT NULL
> start_dt >= from_dt AND <= to_dt
> ELSE
> start_dt >= from_dt AND <= to_dt
> END
>END
>
>|||Hi all, so far i have adopted the following approach.
If no parameters are supplied i want all records to be selected.
At the moment this isn't catered for in the query below.
My overall logic is:
If @.name is provided filter results with @.name
If @.from_dt is provided filter results with @.from_dt
If @.name and @.from_dt are provided filter with both.
@.if no parameters are provided just select all records.
Can anyone give some modifications to this query to achieve this?
Thanx...
SELECT
employee_id, first_name, last_name,
company_rep, user_nme, user_pass
FROM
employee
WHERE
(@.name IS NOT NULL AND first_name + ' ' + last_name LIKE @.name)
OR
(@.name IS NOT NULL AND @.from_dt IS NOT NULL AND start_dt BETWEEN
@.from_dt AND @.to_dt)
OR
(@.name IS NULL AND start_dt BETWEEN @.from_dt AND @.to_dt)
ORDER BY
last_name|||Sounds like you're trying to do this:
CREATE PROCEDURE [dbo].[get_emp_list]
@.name VARCHAR(60) = '%',
@.from_dt SMALLDATETIME = '19000101',
@.to_dt SMALLDATETIME = '20790606'
AS
BEGIN
SELECT
employee_id, first_name, last_name,
company_rep, user_nme, user_pass
FROM
employee
WHERE first_name + ' ' + last_name LIKE @.name
AND start_dt >= @.from_dt
AND start_dt <= @.to_dt
END
This goes along the lines of factor in each parameter in the where
clause but if no value is passed into the proc for each specific
parameter then a default value will be used, for each parameter, such
that it won't limit the resultset at all (ie. any @.name string, the min
@.from_dt value and the max @.to_dt value for the datatypes you chose).
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
AJ wrote:

>Hi all, so far i have adopted the following approach.
>If no parameters are supplied i want all records to be selected.
>At the moment this isn't catered for in the query below.
>My overall logic is:
>If @.name is provided filter results with @.name
>If @.from_dt is provided filter results with @.from_dt
>If @.name and @.from_dt are provided filter with both.
>@.if no parameters are provided just select all records.
>Can anyone give some modifications to this query to achieve this?
>Thanx...
> SELECT
> employee_id, first_name, last_name,
> company_rep, user_nme, user_pass
> FROM
> employee
> WHERE
> (@.name IS NOT NULL AND first_name + ' ' + last_name LIKE @.name)
> OR
> (@.name IS NOT NULL AND @.from_dt IS NOT NULL AND start_dt BETWEEN
>@.from_dt AND @.to_dt)
> OR
> (@.name IS NULL AND start_dt BETWEEN @.from_dt AND @.to_dt)
> ORDER BY
> last_name
>|||One small caveat to Mike's excellent suggestion - the technique he proposes
requires that none of the columns (first_name, last_name, start_dt, and
end_dt columns) be nullable. If any of the rows contains nulls in one or
more of those columns, then you will not match that row. Since you did not
post any DDL, we do not know the details of the table being searched, so
this may or may not apply in your case. In the future, please post DDL and
sample data so you have the best chance of receiving the most complete
answer possible.
Erland Sommarskog has a great article about dynamic search criteria at:
http://www.sommarskog.se/dyn-search.html
ITHT
Jeremy Williams
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:%23xxsVZmaFHA.3400@.tk2msftngp13.phx.gbl...
Sounds like you're trying to do this:
CREATE PROCEDURE [dbo].[get_emp_list]
@.name VARCHAR(60) = '%',
@.from_dt SMALLDATETIME = '19000101',
@.to_dt SMALLDATETIME = '20790606'
AS
BEGIN
SELECT
employee_id, first_name, last_name,
company_rep, user_nme, user_pass
FROM
employee
WHERE first_name + ' ' + last_name LIKE @.name
AND start_dt >= @.from_dt
AND start_dt <= @.to_dt
END
This goes along the lines of factor in each parameter in the where clause
but if no value is passed into the proc for each specific parameter then a
default value will be used, for each parameter, such that it won't limit the
resultset at all (ie. any @.name string, the min @.from_dt value and the max
@.to_dt value for the datatypes you chose).
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
AJ wrote:
Hi all, so far i have adopted the following approach.
If no parameters are supplied i want all records to be selected.
At the moment this isn't catered for in the query below.
My overall logic is:
If @.name is provided filter results with @.name
If @.from_dt is provided filter results with @.from_dt
If @.name and @.from_dt are provided filter with both.
@.if no parameters are provided just select all records.
Can anyone give some modifications to this query to achieve this?
Thanx...
SELECT
employee_id, first_name, last_name,
company_rep, user_nme, user_pass
FROM
employee
WHERE
(@.name IS NOT NULL AND first_name + ' ' + last_name LIKE @.name)
OR
(@.name IS NOT NULL AND @.from_dt IS NOT NULL AND start_dt BETWEEN
@.from_dt AND @.to_dt)
OR
(@.name IS NULL AND start_dt BETWEEN @.from_dt AND @.to_dt)
ORDER BY
last_name|||No, SQL has a CASE expression **not** a CASE statement. Big
difference! Expressionds return a value: they do not control flow of
control.|||Exactly what I was getting at (did you read my whole post?).
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
--CELKO-- wrote:

>No, SQL has a CASE expression **not** a CASE statement. Big
>difference! Expressionds return a value: they do not control flow of
>control.
>
>

Dynamic Query

Hi!

I am trying to dynamically modify my pass-through query containing a
procedure call with 2 parameters.

When I run my access app, I get this error: "Object or provider is not
capable of performing reuqested operation."

Below is my access code:

Dim varItem As Variant
Dim strSQL As String
Dim cat As ADOX.Catalog
Dim cmd As ADODB.Command
Dim strMyDate As String, dtMyDate As Date

dtMyDate = CDate([Forms]![ySalesHistory]![Start Date])
strMyDate = Format(dtMyDate, "yyyymmdd")

strSQL = "procCustomerSalesandPayments '" & strMyDate & "', '" &
[Forms]![ySalesHistory]![Customer Number] & "'"

Set cat = New ADOX.Catalog
Set cat.ActiveConnection = CurrentProject.Connection

'= = >NOTE: THIS IS WHERE THE ERROR POPS OUT!
Set cmd = cat.Procedures("Ben_CustomerSalesandPayments").Command

cmd.CommandText = strSQL
Set cat.Procedures("Ben_CustomerSalesandPayments").Command = cmd

DoCmd.OpenReport stDocName, acViewPreview

Set cat = Nothing
Set cmd = Nothing

Can anyone help me out?

Thanks.Ben (pillars4@.sbcglobal.net) writes:

Quote:

Originally Posted by

I am trying to dynamically modify my pass-through query containing a
procedure call with 2 parameters.
>
When I run my access app, I get this error: "Object or provider is not
capable of performing reuqested operation."

ADOX is nothing I have experience of, but I found in MSDN under the Command
property in ADOX that it says:

An error will occur when getting and setting this property if the
provider does not support persisting commands.

Which provider are you using? How does your connection string look like?

--
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|||Below is the connection string:

ODBC;DSN=YES2;DATABASE=YES100SQLC;

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns99621E8AFE47Yazorman@.127.0.0.1...

Quote:

Originally Posted by

Ben (pillars4@.sbcglobal.net) writes:

Quote:

Originally Posted by

>I am trying to dynamically modify my pass-through query containing a
>procedure call with 2 parameters.
>>
>When I run my access app, I get this error: "Object or provider is not
>capable of performing reuqested operation."

>
ADOX is nothing I have experience of, but I found in MSDN under the
Command
property in ADOX that it says:
>
An error will occur when getting and setting this property if the
provider does not support persisting commands.
>
Which provider are you using? How does your connection string look like?
>
--
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

|||Ben (pillars4@.sbcglobal.net) writes:

Quote:

Originally Posted by

Below is the connection string:
>
ODBC;DSN=YES2;DATABASE=YES100SQLC;

And what is in that DSN?

Particular which OLE DB provider do you use? I had a look in a book on
ADO, and it said that the only two providers to support ADOX are the
Jet provider and SQLOLEDB. The book is a bit old, but if ODBC means that
you are using MSDASQL, then we have the answer to your problem. Change
to use SQLOLEDB instead.

--
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

Dynamic number of columns.

Hi all.
I do have a "simple" problem on my hand.
I have one table containing PriceGroups. It could be 1 or many PriceGroups
pr. produkt. When I list the produkts data in one record I want the
PriceGroup description to be columns as well with the corresponding price. A
kind of dynamic Pivot JOIN with the productrecord.
E.g if there are 3 pricegroups to this produkt I would get 3 lines using
standard join.
ProductNr PricegroupDesc Price
ab Price1 200
ab Price2 300
ab Price3 500
What I want is 1 line with the 3 descriptions as columnheader and the
corresponding prices under.
ProductNr Price1 Price2 Price3 ..........PriceN
ab 200 300 500 nnnnnn
Thanx all.
geirhttp://www.aspfaq.com/2462
David Portas
SQL Server MVP
--|||Thank you David.
This got me startet on something I can use.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108636820.313261.178950@.c13g2000cwb.googlegroups.com...
> http://www.aspfaq.com/2462
> --
> David Portas
> SQL Server MVP
> --
>

Dynamic filedlength of varchar fields in source

I'm using SSIS to import data from a table (SQL) containing varchar fields. The problem is, that those varchar fields are changing over time (sometimes shrinking and sometimes expanding). I.e. from varchar(16) to varchar(20).

When I create my SSIS package, the package seem to store information about the length of each source-field. At runtime, if the field-length is larger then what the package expects an error is thown.

Is there anyway around this problem?

Oh, yeah... My destination fields are a lot wider then the source fields, so the problem is not that the varchar values doesn't fit in my destination table, but that the package expects the source to be smaller...

Regards Andreas

You cannot dynamically change the metadata in an SSIS package -- it needs to be fixed at design time.

You could try casting the source fields to the size they will be on the destination, and hope that the source fields never exceed this size. Or, you could castthe source fields to DT_TEXT so that length can be variable, but performance might suffer (there is extra processing going on for long-object types like DT_TEXT).

Thanks
Mak

|||

A. Brosten wrote:

I'm using SSIS to import data from a table (SQL) containing varchar fields. The problem is, that those varchar fields are changing over time (sometimes shrinking and sometimes expanding). I.e. from varchar(16) to varchar(20).

When I create my SSIS package, the package seem to store information about the length of each source-field. At runtime, if the field-length is larger then what the package expects an error is thown.

Is there anyway around this problem?

Oh, yeah... My destination fields are a lot wider then the source fields, so the problem is not that the varchar values doesn't fit in my destination table, but that the package expects the source to be smaller...

Regards Andreas

You can change the SSIS package so that the external metadata stored within there is large enough for all eventualities.

-Jamie

Dynamic Cursor versus Forward Only Cursor gives Poor Performance

Hello,

I have a test database with table A containing 10,000 rows and a table
B containing 100,000 rows. Rows in B are "children" of rows in A -
each row in A has 10 related rows in B (ie. B has a foreign key to A).

Using ODBC I am executing the following loop 10,000 times, expressed
below in pseudo-code:

"select * from A order by a_pk option (fast 1)"
"fetch from A result set"
"select * from B where where fk_to_a = 'xxx' order by b_pk option
(fast 1)"
"fetch from B result set" repeated 10 times

In the above psueod-code 'xxx' is the primary key of the current A
row. NOTE: it is not a mistake that we are repeatedly doing the A
query and retrieving only the first row.

When the queries use fast-forward-only cursors this takes about 2.5
minutes. When the queries use dynamic cursors this takes about 1 hour.

Does anyone know why the dynamic cursor is killing performance?
Because of the SQL Server ODBC driver it is not possible to have
nested/multiple fast-forward-only cursors, hence I need to explore
other alternatives.

I can only assume that a different query plan is getting constructed
for the dynamic cursor case versus the fast forward only cursor, but I
have no way of finding out what that query plan is.

All help appreciated.

KevinPlease explain what you are trying to do here. Cursors are usually best
avoided and typically perform much less efficiently than set-based
solutions. If you describe the problem in more detail someone should be able
to suggest an alternative that doesn't use a cursor. Post DDL (CREATE TABLE
statements), some sample data (INSERT statements) and show your required
result.

--
David Portas
SQL Server MVP
--

Dynamic connect...?

I am trying to implement a web application user login system where every user is an Oracle user, so I can avoid having tables containing passwords and what not. In fact, having passwords in a table is not an option, even if they're encrypted. Anyway, I'm trying to set it up so that the login page is under a DAD that logs in as a user with rights to the login package only. Then, once the user has typed in their name and password and submitted, I want to then log them in as their user that has already been created in Oracle.

The first part is easy enough, but I have tried unsuccessfully to find some way to use dynamic sql to change users, such as EXECUTE IMMEDIATE 'CONNECT user/pass@.db'; and concatenating the appropriate values, but nothing seems to work.

I'm trying to avoid the basic authentication dialog box, as well as avoiding storing passwords in tables. I have looked into the custom authorization stuff provided by owa_custom, but I can't see any way to implement it with Oracle users. Any help on this would be greatly appreciated. Thanks!"connect" is a SQL*Plus command and hence cannot be executed dynamically. Other examples would be "show user", "desc table" etc. Only sql commands can be executed using dynamic sql.

But if you have the uid & pwd, can you not connect from your web application to see if the user is a valid database user or not ?|||Yeah, that is an option. I'm trying to avoid using JDBC or anything like that. However, if need be, I suppose that is something I can try. If there are any other ways to connect from the app, I would be interested in hearing about them. My experience with web application login systems is extremely limited, so any kind of help is greatly appreciated.