New to Stored Procedures

[shaminda]

I need to change the following code in VB to a stored proceduer. What changes do I have to do in VB and SQL Server?

Dim rr as ADODB.Recordset
Set rr = New ADODB.Recordset

int866 = Me.lst866.ListIndex
SQL = "SELECT DISTINCT [RACK_NO] FROM [TABLE1]
WHERE [866ID] = '" & int866 & "' AND [IN_SYSTEM] IS NULL
ORDER BY [RACK_NO] ASC;"

rr.Open SQL, con_VB, adOpenDynamic, adLockOptimistic

 

[crystalized]

create procedure procName
(
@int866 int
)
As
Select distinct rack_no from table1
where 866ID=@int866 and in_system is null
order by rack_no asc


At least that is what I would do, make your one dynamic comparison item into a parameter for the procedure. You could also set a default value for the parameter by using

@int866 int=0

as an example.

Hope this helps

Crystal
crystalized_s@yahoo.com

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

Experience is one thing you can't get for nothing.

-Oscar Wilde

 

[crystalized]

Sorry I also always end the stored procedure with
the keyword "return". I am fairly new to stored procedures myself so I am unsure as to whether it is required or not but I use it anyway
Crystal
crystalized_s@yahoo.com

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

Experience is one thing you can't get for nothing.

-Oscar Wilde