Passing in a Parameter

Hi All,
I have a Stored Proc and would like to pass in a VARCHAR parameter. The
problem is that the query will need to use it in a IN clause...
DECLARE @.PARAM AS VARCHAR(20)
SET @.PARAM = 'Dan', 'Mike', 'Lisa'
SELECT *
FROM Orders
WHERE Name IN (@.PARAM)
is there a away around this?
Thanks,
Kunkel
On Thu, 28 Apr 2005 08:04:09 -0700, Kunkel wrote:

>Hi All,
>I have a Stored Proc and would like to pass in a VARCHAR parameter. The
>problem is that the query will need to use it in a IN clause...
>DECLARE @.PARAM AS VARCHAR(20)
>SET @.PARAM = 'Dan', 'Mike', 'Lisa'
>SELECT *
>FROM Orders
>WHERE Name IN (@.PARAM)
>is there a away around this?
Hi Kunkel,
Several. And they are all described and compared at
http://www.sommarskog.se/arrays-in-sql.html
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||thanks for that link! it was very helpful. but after i implemented the
articles ideas, i came across this solution:
DECLARE @.Var AS VARCHAR(100)
SET @.Var = '''Dan'', ''Mike'', ''Lisa'''
DECLARE @.SQL AS varchar(1000)
SET @.SQL = 'SELECT *
FROM ORDERS
WHERE
NAME IN (' + @.Var + ')'
Exec(@.SQL)
"Hugo Kornelis" wrote:

> On Thu, 28 Apr 2005 08:04:09 -0700, Kunkel wrote:
>
> Hi Kunkel,
> Several. And they are all described and compared at
> http://www.sommarskog.se/arrays-in-sql.html
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Thu, 28 Apr 2005 15:30:02 -0700, Kunkel wrote:

>thanks for that link! it was very helpful. but after i implemented the
>articles ideas, i came across this solution:
>DECLARE @.Var AS VARCHAR(100)
>SET @.Var = '''Dan'', ''Mike'', ''Lisa'''
>DECLARE @.SQL AS varchar(1000)
>SET @.SQL = 'SELECT *
>FROM ORDERS
>WHERE
>NAME IN (' + @.Var + ')'
>Exec(@.SQL)
Hi Kunkel,
This is dynamic SQL, and I believe that this techinique is discussed at
Erland's site as well. Please don't do this if you can avoid it. It is a
severe breach of security - you give malicious users the ability to
inject SQL.
Erland has a great article with an in-depth explanation of pros and cons
of dynamic SQL as well: http://www.sommarskog.se/dynamic_sql.html
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Passing date Value

Hi,
I have a problem while passing a date value to datefunction.My query is like that ..
declare @.todate varchar(20),@.FromDate varchar(20)
set @.todate='18/10/2005'
set @.FromDate = '11/10/2005'
print DATEADD(yy, DATEDIFF(yy,0,convert(datetime,@.todate)), 0)
But i getting the error like this..
Server: Msg 242, Level 16, State 3, Line 4
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Can any body help me ..
Thanks and Regards
AJOSH

See my post
http://forums.asp.net/1083329/ShowPost.aspx

passing a query into a variable

What is the easiest way to pass a value into a variable...
TSQL...
declare @.@.test int
--select count(*) from authors
select count(*) into @.@.test from authorsstupid question... resolved it... guys...
my apologies....

pass in null/blank value in the date field or declare the field as string and co

I need to pass in null/blank value in the date field or declare the field as string and convert date back to string.

I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits.
The mfg_start_date is delcared as a string variable

mfg_start_date = CStr(CDate(Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Mid(rs_get_msp_info(2), 1, 2)))

option 1
I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value.

With refresh_shipping_sched
.ActiveConnection = CurrentProject.Connection
.CommandText = "spRefresh_shipping_sched"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("@.option", adInteger, adParamInput, 4, update_option)
.Parameters.Append .CreateParameter("@.mfg_ord_num", adChar, adParamInput, mfg_ord_num_length, "")
.Parameters.Append .CreateParameter("@.mfg_start_date", adChar, adParamInput, 10, "")
Set rs_refresh_shipping_sched = .Execute
End

Please helpThe stored procedure will accept null if you define the parameter that way:

create procedure TESTPROCEDURE (@.TestDate datetime = NULL)
as
select @.TestDate
go

exec TESTPROCEDURE '1/1/2003'
go

exec TESTPROCEDURE
go

blindman|||I think vbNull also works when passing in a parameter. The code you have below is passing in an empty string which I'm sure I don't have to tell you is not null.

Try the following:
.Parameters.Append .CreateParameter("@.mfg_start_date", adChar, adParamInput, 10, vbNull)

I think the stored proc idea is better though, it's safer and better for your data integrity.

Dan|||Originally posted by danielacroft
I think vbNull also works when passing in a parameter. The code you have below is passing in an empty string which I'm sure I don't have to tell you is not null.

Try the following:
.Parameters.Append .CreateParameter("@.mfg_start_date", adChar, adParamInput, 10, vbNull)

I think the stored proc idea is better though, it's safer and better for your data integrity.

Dan

Hello Dan,

What I need is an empty string in the date field to pass in in the stored procedure. What is the vb code for that?

Thanks!|||The code to pass null (empty string won't work and null will only work if you have allowed nulls on this column in your db design) for a parameter is this:

.Parameters.Append .CreateParameter("@.mfg_start_date", adChar, adParamInput, 10, vbNull)

I modified your existing code. I'm not 100% sure that this will work but it should.

Dan|||Originally posted by danielacroft
The code to pass null (empty string won't work and null will only work if you have allowed nulls on this column in your db design) for a parameter is this:

.Parameters.Append .CreateParameter("@.mfg_start_date", adChar, adParamInput, 10, vbNull)

I modified your existing code. I'm not 100% sure that this will work but it should.

Dan

Thanks for replying so quickly.
I edited my code as you have it above.
I'm stilll having trouble getting the date displaying correctly. I need the year to display in four digits. It displays something '12/31/03'
This is code that I have

Function get_date(mfg_start_date as string,..)
mfg_start_date = Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Mid(rs_get_msp_info(2), 1, 2)
mfg_start_date = CStr(Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Year(mfg_start_date))

mfg_start_date is the textbox I need the date field to display but it will only eight digits of the year and place two empty strings after. I can't understand why. In the db design the mfg_start_date field is a char with length 10 as in the stored procedure.

Also there must be a better way to write the code that I have above.

Thank you again.|||The date format is normally determined by the locale settings ont he server when you're using VB. Can I ask why you're not using a date field in your database?

Dan

Pass Group into variable then query NOT IN variable?

Greetings all.

I'm looking for a clever way to pass a group into a variable and then query the variable using NOT IN

For example

DECLARE @.myvariable varchar(100)

SET @.myvariable = (1, 2, 3, 4, 5)

SELECT * FROM myTable

WHERE myField NOT IN @.myvariable

I swear I've seen this and I'm sure it's simple but my brain is not calculating the algorithm.

Thanks for all help,

Adamus

This works, but does require the dreaded dynamic SQL:

if object_id('myTable') is not null drop table myTable

go

create table myTable (myField int)

go

insert myTable values (1)

insert myTable values (2)

insert myTable values (3)

insert myTable values (4)

insert myTable values (5)

insert myTable values (6)

insert myTable values (7)

go

DECLARE @.myvariable varchar(100)

SET @.myvariable = '(1, 2, 3, 4, 5)'

exec('SELECT * FROM myTable WHERE myField NOT IN ' + @.myvariable)

Ron

|||

try

Code Snippet

DECLARE @.myvariable varchar(100),

@.stmt varchar(8000)

SET @.myvariable = (1, 2, 3, 4, 5)

SET @.stmt = 'SELECT * FROM myTable WHERE myField NOT IN ' + @.myvariable

exec sp_sqlexec @.stmt

|||

That's it!

Thanks,

Adamus

|||

Rice31416,

Unfortunately, your example demonstrates the use of "the dreaded dynamic SQL." That what you are executing in the EXEC() function.

Perhaps you meant something else...

|||

Dale,

I suspect you meant:

EXECUTE sp_executesql @.Stmt

|||

Yup, thanks.

Leftover from days gone by. Also need to change varchar to nvarchar...

|||

Arnie Rowland wrote:

Rice31416,

Unfortunately, your example demonstrates the use of "the dreaded dynamic SQL." That what you are executing in the EXEC() function.

Perhaps you meant something else...

This was the answer I was looking for but what do you mean by "unfortunately?"

Is there something bad about using this approach?

Adamus

|||

Hi Adam,

Using Dynamic SQL is sometimes a necessary 'evil'. At least some folks consider its usage to be tandamont to 'evil'. (My comment about the "dreaded" was to reflect back to Rice31416 that while he wrote that his approach didn't use "the dreaded dynamic SQL", in fact, his approach was using dynamic SQL."

I recommend this article from Erland Sommarskog. It gets into advanced issues, but keep it for reference.

Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html

|||

Arnie,

Yes, I meant that it *does* involve dynamic SQL. And yes, I got lazy and used "execute" in the example instead of sp_executesql!

Ron

pass a value

is it possible to do:
(A)
declare @.numberofitems Int
@.numberofitems = select max(itemorder)
from store, department, etc.

and pass the @.numberofitems to a #tempStore table, like:
(B)
(store, department, @.numberofitems,...)

I got itemorder but not the number of items in each department
Alex

--
Sent by 3 from yahoo part from com
This is a spam protected message. Please answer with reference header.
Posted via http://www.usenet-replayer.com/cgi/content/newHi

You don;t give enough detail to produce an exact query. Please post DDL,
example data (as insert statements) and expected output if you require a
more precise answer.

At a guess something like:

SELECT S.Store, D.Department, max(I.itemorder) as NumberOfItems
from store S JOIN department D ON S.StoreId = D.StoreId
JOIN ItemsOrders I On I.DeptId = D.DeptId
GROUP BY S.Store, D.Department

or

SELECT S.Store, D.Department, ( SELECT max(I.itemorder) FROM ItemsOrders I
WHERE I.DeptId = D.DeptId AND S.StoreId = I.StoreId ) as NumberOfItems
FROM store S JOIN department D ON S.StoreId = D.StoreId

John

etc"alexqa2003@.yahoo.com" <u128845214@.spawnkill.ip-mobilphone.net> wrote in
message news:l.1062471242.1626678466@.host-66-81-78-52.rev.o1.com...
> is it possible to do:
> (A)
> declare @.numberofitems Int
> @.numberofitems = select max(itemorder)
> from store, department, etc.
> and pass the @.numberofitems to a #tempStore table, like:
> (B)
> (store, department, @.numberofitems,...)
> I got itemorder but not the number of items in each department
> Alex
>
>
> --
> Sent by 3 from yahoo part from com
> This is a spam protected message. Please answer with reference header.
> Posted via http://www.usenet-replayer.com/cgi/content/new|||u128845214@.spawnkill.ip-mobilphone.net (alexqa2003@.yahoo.com) wrote in message news:<l.1062471242.1626678466@.host-66-81-78-52.rev.o1.com>...
> is it possible to do:
> (A)
> declare @.numberofitems Int
> @.numberofitems = select max(itemorder)
> from store, department, etc.
> and pass the @.numberofitems to a #tempStore table, like:
> (B)
> (store, department, @.numberofitems,...)
> I got itemorder but not the number of items in each department
> Alex

It's not really clear from your post what you're trying to do, but it
may be something like this:

insert into #tempStore
(store, department, numberofitems)
select store, department, max(itemorder)
from orders
group by store, department

Or maybe this:

insert into #tempStore
(store, department, numberofitems)
select store, department, count(itemorder)
from orders
group by store, department

If this doesn't help, then it would be good if you can post your DDL
(CREATE TABLE statements), along with some sample data and the
expected output.

Simon