SP help urgent!!

  • Thread starter Thread starter alpha
  • Start date Start date
A

alpha

Hi SQL GURUS,
When I am executing following SP in sql2000 its giving "Must declare the variable '@Emp1'." even though it is declared. I need dynamic query because IN parameter will be passed at run time as input parameter to SP. Its working if table used is already there in database instead to temp table declared inside SP.

Databse used is" Northwind" and Table -- "Employees"
CREATE PROCEDURE spTest
@EmployeeName varchar(500)
AS
BEGIN
DECLARE @EmployeeNameFilter TABLE(AccountID varchar(400))
DECLARE @query VARCHAR(500)
SET @query = 'INSERT @Emp1 SELECT EMPLOYEEID FROM Employees WHERE employeeid IN ( '+@EmployeeName+' )'
EXEC ( @query)

END

exec spTest '1,2,3,4,5,6'

Thanks,
 
Hi alpha,

I think that you can't use parameters in dynamic generated queries in first
place.
Plus, you haven't declare @Emp1.
Plus, the sql statament is odd.

Check out the solution Willam Ryan proposed few days ago in thread:
Using Params with an IN Statemet...Feedback


--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com


alpha said:
Hi SQL GURUS,
When I am executing following SP in sql2000 its
Click to expand...
giving "Must declare the variable '@Emp1'." even though it is declared. I
need dynamic query because IN parameter will be passed at run time as input
parameter to SP. Its working if table used is already there in database
instead to temp table declared inside SP.
Databse used is" Northwind" and Table -- "Employees"
CREATE PROCEDURE spTest
@EmployeeName varchar(500)
AS
BEGIN
DECLARE @EmployeeNameFilter TABLE(AccountID varchar(400))
DECLARE @query VARCHAR(500)
SET @query = 'INSERT @Emp1 SELECT EMPLOYEEID FROM Employees WHERE
Click to expand...
employeeid IN ( '+@EmployeeName+' )'
EXEC ( @query)

END

exec spTest '1,2,3,4,5,6'

Thanks,
Click to expand...
Community Website: http://www.dotnetjunkies.com/newsgroups/
 
Hi,

There are at least 2 ways to do this: using dynamic sql or using 'dynamic
creation of an sp' from .net.

Here's the first:
CREATE PROC GetOrderList1
(
@OrderList varchar(7999)
)
AS
BEGIN
SET NOCOUNT ON

DECLARE @SQL varchar(600)

SET @SQL =
'SELECT bipad, issuecode, draw
FROM histd
WHERE bipad IN (' + @OrderList + ')'

EXEC(@SQL)
END

I think your line 'insert @emp1' is the problem line: @emp1 has not been
declared.

Here's another way - I think a better way:
I ran into this issue long ago and came up with what I think is a very
useful workaround (which I constantly employ): the inability to pass an
array to an sp was the impetus for this: I create an sp 'on the fly' - my
own form of dynamic sql - and pass a string that represents the array into
the sql select. Both the size of an sp in SQL 2000 and the string size make
this almost fullproof - you would need an array with hundreds of thousands
of elements and an sp the size of the Grand Canyon to make this fail.
Here's the code:

longstring = "('"

For j = 0 To i - 1 'i is the count of elements in both of the arraylists
referred to below

longstring = longstring & RTrim(titlearray(j)) & RTrim(issuecodearray(j)) &
Chr(39) & ",'"

Next

longstring = Mid(longstring, 1, longstring.Length - 2)

longstring = longstring & ")"

Dim docmd As New SqlCommand

docmd = New SqlCommand("exec sp_dropgensum", oconn)

Try

docmd.ExecuteNonQuery()

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

docmd = New SqlCommand("exec sp_dropsp_copyintogensum", oconn)

Try

docmd.ExecuteNonQuery()

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

Dim creationstring As String

creationstring = "CREATE PROCEDURE sp_copyintogensum AS " _

& "select * into gensum from invdet where substring(imcacct,1,5) = '30544'
and rtrim(title) + rtrim(issuecode) in " & longstring

Dim sqladaptdel As New SqlDataAdapter

sqladaptdel.SelectCommand = New SqlCommand(creationstring, oconn)

'this creates the new sp

Try

sqladaptdel.SelectCommand.ExecuteNonQuery()

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

' then i run the new sp

Dim addcmd As New SqlCommand

addcmd = New SqlCommand("sp_copyintogensum", oconn)

addcmd.CommandType = CommandType.StoredProcedure

Try

addcmd.ExecuteNonQuery()

Catch ex As Exception

MessageBox.Show(ex.message)

End Try

HTH,

Bernie Yaeger

alpha said:
Hi SQL GURUS,
When I am executing following SP in sql2000 its
Click to expand...
giving "Must declare the variable '@Emp1'." even though it is declared. I
need dynamic query because IN parameter will be passed at run time as input
parameter to SP. Its working if table used is already there in database
instead to temp table declared inside SP.
Databse used is" Northwind" and Table -- "Employees"
CREATE PROCEDURE spTest
@EmployeeName varchar(500)
AS
BEGIN
DECLARE @EmployeeNameFilter TABLE(AccountID varchar(400))
DECLARE @query VARCHAR(500)
SET @query = 'INSERT @Emp1 SELECT EMPLOYEEID FROM Employees WHERE
Click to expand...
employeeid IN ( '+@EmployeeName+' )'
EXEC ( @query)

END

exec spTest '1,2,3,4,5,6'

Thanks,
Click to expand...
Community Website: http://www.dotnetjunkies.com/newsgroups/
 
@Emp1 is not declared in the proc. You are saying

INSERT @Emp1
SELECT EMPLOYEEID FROM Employees WHERE employeeid IN (1,2,3,4,5,6)

I assume you are trying an INSERT INTO and @Emp1 is some magical place you
are determining at runtime?

The WHAT is very important here to determine the HOW.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

**********************************************************************
Think Outside the Box!
**********************************************************************
alpha said:
Hi SQL GURUS,
When I am executing following SP in sql2000 its
Click to expand...
giving "Must declare the variable '@Emp1'." even though it is declared. I
need dynamic query because IN parameter will be passed at run time as input
parameter to SP. Its working if table used is already there in database
instead to temp table declared inside SP.
Databse used is" Northwind" and Table -- "Employees"
CREATE PROCEDURE spTest
@EmployeeName varchar(500)
AS
BEGIN
DECLARE @EmployeeNameFilter TABLE(AccountID varchar(400))
DECLARE @query VARCHAR(500)
SET @query = 'INSERT @Emp1 SELECT EMPLOYEEID FROM Employees WHERE
Click to expand...
employeeid IN ( '+@EmployeeName+' )'
EXEC ( @query)

END

exec spTest '1,2,3,4,5,6'

Thanks,
Click to expand...
Community Website: http://www.dotnetjunkies.com/newsgroups/
 
No even after @emp1 is delcared it is giving the same error. Actually it was only a typing mistake in my earlier code. So plz now suggest me some alternative to the code which I gave ealier.


With Regards,
Alpha
 
Back
Top