> > Hi i have created a stored procedure like the second attempt at
> > creating one, only this time i want to pass up to 5 values from my
> > page to the stored procedure to return a recordset that i can write
> > back to the page.
> > i call the procedure with :
> > strSQL = "EXECUTE sp_sel_companynames compname = '" & txtComp & "',
> > compno = '" & cono & "', regpcode = '" & regPcde & "', tradpcode =
> > '" & tradPcode & "', telno = '" & telNo & "'"
> > This is the 5 values i am passing into the procedure, i have based the
> > stored procedure on one in the following page
> > [url]http://www.sommarskog.se/dyn-search.html#dynintro[/url]
> > my stored procedure is as follows:
> > CREATE PROCEDURE sp_sel_companynames
> > compname varchar(50) = null,
> > compno varchar(5) = null,
> > regpcode varchar(10) = null,
> > tradpcode varchar(10) = null,
> > telno varchar(12) = null
> > AS
> > DECLARE sql nvarchar(4000)
> > SELECT sql = 'SELECT CMPNY_NO, CMPNY_NAME, PREV_CMPNY_NAME,
> > REG_POSTCODE, STATUS, TRAD_POSTCODE, TELEPHONE FROM Company WHERE 1 =
> > 1'
> > IF compname IS NOT NULL
> > SELECT sql = sql + '( AND CMPNY_NAME like compname%)'
> > IF compno is not null
> > SELECT sql=sql + '(CMPNY_NO like compno)'
> > IF regpcode is not null
> > SELECT sql=sql + '(REG_POSTCODE like regpcode)'
> > IF tradpcode is not null
> > SELECT sql=sql + '(TRAD_POSTCODE like tradpcode)'
> > IF telno is not null
> > SELECT sql=sql + '(TELEPHONE like telNo)'
> > GO
> > when i run the statement in query yzer it says :
> > Server: Msg 137, Level 15, State 2, Line 1
> > Must declare the variable 'sql'.
> > I have declared this in the procedure. If anyone can point me in the
> > right direction and help me realise where it is wrong i would be
> > really grateful.
> > thanks in advance for any help