How to use Arrarys in SQL with other variables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to use Arrarys in SQL with other variables

Author

Topic

TMDOSS
Starting Member

33 Posts

Posted - 2008-11-10 : 09:42:53

I'm trying to get the data from the table
between two dates and for the List of Doctors

select * from tbl_test
where convert(char(10),createdate,121) between '2008-01-01'
and '2008-12-31' and id_doctor in (1,2,3,4,5)

I want to use two variables for the dates and one array for id_doctors

pl Help

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-11-10 : 11:11:22

If you are looking to pass the data in a array, you must use dynamic Sql and pass a charector string then generate a sql query off of it as in.
[code]

Create proc sp_Test
@myval varchar(20)
as

Declare @SqlStr varchar(2000)

set @SqlStr = 'Select * from Tbl_Test a where a.id_Doctor in (' + @Myval + ')'
exec @SqlStr
go

exec Sp_Test '1,2,3,4,5'
[code]

The propper way to pass the data is by populating a variable table and using that from within the procedure by simply joining it. Either way will work, just as a rule of thumb it's best to only use dynamic sql when needed.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

TMDOSS
Starting Member

33 Posts

Posted - 2008-11-10 : 11:21:15

I Can do that. but I can't use the date variables in the Stored Procedure

When I tried to use date variables in the set @sqlstr I'm getting the error
convert(char(10),createdate,121) between '2008-01-01'
and '2008-12-31'
I think it's better to use the dynamic sql.

Posted - 11/10/2008 : 11:11:22
--------------------------------------------------------------------------------

If you are looking to pass the data in a array, you must use dynamic Sql and pass a charector string then generate a sql query off of it as in.
[code]

Create proc sp_Test
@myval varchar(20)
as

Declare @SqlStr varchar(2000)

set @SqlStr = 'Select * from Tbl_Test a where createdate between @fromdate and @todate a.id_Doctor in (' + @Myval + ')'
exec @SqlStr
go

exec Sp_Test '1,2,3,4,5'
[code]

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-11-10 : 14:04:34

it's the same concept.


Create proc sp_Test
@myval varchar(100)
@myDate varchar(100)
as

Declare @SqlStr varchar(2000)

set @SqlStr = 'Select * from Tbl_Test a where createdate between @fromdate and @todate a.id_Doctor in (' + @Myval + ') and a.Createdate between ' + @Mydate
exec @SqlStr
go

exec Sp_Test '1,2,3,4,5','''1/1/2008'' and ''12/31/2008'''

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources