Dynamic SQL and string as parameter

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

Transact-SQL (2000)

Dynamic SQL and string as parameter

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-01-19 : 09:48:26

Leia writes "Hi!
I'm having trouble figuring this out. I read your FAQ on Dynamic SQL and it gave me some ideas to play with, but at the end I still didn't come up with the solution. I hope you'll be able to help me.

Here's what I'm trying to do.

Lets say I'm writing this stored procedure and I want to use it to create Crystal Report.

Create Procedure sp_temp
@client_code varchar(255)
AS

select matter, employee_code
from matter ,persnl
where matter.empl=persnl.empl
and client in (@client_code)

The problem is that user wants to enter several client codes at the same time, i.e. not just say client 12345, but

12345,01397,15900

So problem # 1

When I run this procedure I type

sp_temp '12345,01397,15900'

and then code takes that string, puts it into select statement and I get nothing as the result.

Problem #2

Even if code would understand that string, I need to do something with code like 01397, because without "" it's gonna be read as 1397 and I need somehow to force it to read as 01397.

Of course user doesn't want to type "" around numbers that starts with 0, because it's too much typing!!

So this is two problems that I got stuck with. Can you help?

Thank you kindly,

Leia"

nr
SQLTeam MVY

12543 Posts

Posted - 2004-01-19 : 10:59:11

',' + @client_code + ',' like '%,' + client + ',%'

or use a function to create a table of the codes
http://www.nigelrivett.net/ParseCSVString.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2004-01-19 : 10:59:46

What's the data type of the field? If it's char/varchar, then you need to convert the user input to that format (including spaces/leading zeros) to match the column.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-01-19 : 11:01:00

n.b. if client is an int and is entered padded with 0's then
',' + @client_code + ',' like '%,' + right('0000' + convert(varchar(5),client),5) + ',%'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-01-19 : 12:20:24

In crsytal, you can allow the users to choose a list of values just for this purpose. it then returns an array of the values in the list that the user has chosen. Look at more of the parameter options available to you.

not sure if that will work with a stored procedure, though -- but it will definitely work if you can write your report logic as a view.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources