Stored Proc + DateTime params

[smitan]

Hi, I try to write a query qith some parameters based on a user record in a table.

Select * 
From tblUser 
Where UserId=@Param1

This gives the parameters for the rest of the SP.
As there are a number of parameters I would rather change the querytext then have many queries.

Like:

if UserField=1
   Select * 
   From table1
else
   Select * 
   From table2

I would prefer:

Declare @SQL as navarchar(1000)

If UserField=1
   Select @SQL='Select * 
                From table1'
Else
   Select @SQL='Select * 
                From table2'

Exec @SQL

This is a simplified exampel.
In the real world, my queries have parameters of which several are dates:

Select @SQL='Select * 
             From table2 
             Where (DateField1 Between userParam1 And
                    userParam2) And 
                    DateField2 = userParam3'

I have no problem in having the thing running when I use straight Select-queries in my SP, but when using my 'indirect' way (via @SQL) I get conversion errors ('Syntax error converting datetime from characterstring') all the time, whatever I try.

Solutions to the problem are most welcome.

Thanks
Smitan

 

[SQLDenis]

everything that is inside @SQL has to be a varchar
I assumed userParam1,2,3 are parameters like @userParam1 and they are of type datetime
so here we go

Select @SQL='Select * 
             From table2 
             Where (DateField1 Between ' + convert(varchar,@userParam1 + ' And ' +
                     convert(varchar,@userParam2) +') And 
                    DateField2 = ' +  convert(varchar,@userParam3)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[smitan]

Hi Denis, thanks a lot.
Good solution for the query text.

Unfortunately the query does not give the expected results.
Do you have any suggestion how I can see the query text?

Tried with

print (@SQL)

and

print @SQL


But neither gave any result.

Thanks
Smitan

 

[SQLDenis]

If you can't see a result that means one or more of your parameters is/are null
put coalesce around them to see which ones are null

instead of convert(varchar,@userParam1)
do
convert(varchar,coalesce(@userParam1,0))
or
convert(varchar,coalesce(@userParam1,''))

depending on the data type

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[smitan]

Hi again,

the actual statement I am using, in a test, is:

set @SQL ='select PName, PDate
from tblPBase
where PDate between 2005/01/01 and ' +
convert(varchar,coalesce(@parameter1)) + '
Order By PDate desc'

I type the param: '2005/06/06' as, not to have problems in confusing mm with dd.

I would very much like to have a PRINT possibility, so I can see the result in @SQL.
There are no errors, so that's better already

 

[smitan]

Hi Denis,

to make the result visible I stored it into a table.

Not perfect, but it helps.

Found out that my testparameter only was 1 char.
Changed it to 10 and the result was a nice query.

Added a char(39) around the dates and it works like a dream.

Do you have any solution on the PRINT, to get some output....?
Thanks you very much.

Have a nice weekend.
Smitan

 

[SQLDenis]

If you get output then the print should work also
BTW the print will just print out the string not the results of course, example
declare @id int,@SQL varchar(500)
set @id =1
set @SQL ='id =' +comvert(varchar,@id)
print @SQL will print out id =1

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/