RE: SQL Stored Proc

[Smith, Matthew P -CONT(CSC)]

you have a lot of options really.  loop over the list, stick it into a table var or temp table, and do a subselect.  play around with it.

(not u neil :P)
-----Original Message-----
From: Robertson-Ravo, Neil (RX)
To: CF-Talk
Sent: 3/2/04 11:07 AM
Subject: RE: SQL Stored Proc

Two things,  

Modify the create statement to be :

CREATE PROCEDURE spTest @arg_DivID CHAR(255)

Its also better practice not to name a user stored procedure with the
prefix
"sp_"  - use "usp_" or something else which does not conflict with
system
naming.

You cannot convert a comma seperated string into a number - its a string
not
a number.


   _____  

From: Doug Kronenbergeer [mailto:[EMAIL PROTECTED]
Sent: 02 March 2004 15:56
To: CF-Talk
Subject: SQL Stored Proc

I'm having trouble passing parameters to a stored procedure in mssql.

How do I pass a the string "1,2,3" so that it can be convert into the
tinyint. Needless to sa DivisionID is a tinyint.

This is the sp

-------------------------------------------------

CREATE PROCEDURE spTest
(
@arg_DivID CHAR(255)
)

AS

SELECT     TOP 100 PERCENT EmpID, LastName, FirstName, MaxDate,
DivisionID,
DivisionAbrv, OfficeID, OfficeAbrv, ProjectID, ProjectAbrv
FROM         dbo.Lvl2ReqEmpInfo
WHERE     (DivisionID IN (@arg_DivID)) AND (OfficeID IN (1, 2, 3, 4, 5,
6,
7, 8, 9, 10, 11, 12, 13, 14)) AND (ProjectID IN (0, 1, 2))
ORDER BY DivisionAbrv, OfficeAbrv, ProjectAbrv, LastName, FirstName
   _____
  _____

---

[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]

---