Pass through query with declare doesn't return records
I need a pass through query (to a SQL table) in where I have declared a variable. I can run the query in Query Analyzer with a record returning, however as a pass through I get the message like 'Pass Through Query with ReturnRecords set to True did not return any records'.
I know I could do this as a StoredProcedure and then have the pass through 'exec sp_Name paramValue' but the sql statement is going to be generated in VBA depending on what is selected on a form.
The attached code is not the full statement I need, just testing if declare's worked
I know I could do this as a StoredProcedure and then have the pass through 'exec sp_Name paramValue' but the sql statement is going to be generated in VBA depending on what is selected on a form.
The attached code is not the full statement I need, just testing if declare's worked
declare @ProjectID int
select @ProjectID = 6233
Select * From Project Where ProjectID = @ProjectID
ASKER
Nope, set is getting the same result as Select
r u using sql server or Access ?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
It looks to me that in Access 2000 it is getting to the first SELECT (or SET if you assign it this way) and is expecting that as the records returned.
My exampl below returns OK EmployeeID = 1 but if I put the SET above the first SELECT then I get the same Error and if I remove the set completely then I get the records where EmployeeID = 1
This looks like you will need to use VBA to code round this limitation
CurrentDb.QueryDefs("MyQue
Cheers, Andrew
My exampl below returns OK EmployeeID = 1 but if I put the SET above the first SELECT then I get the same Error and if I remove the set completely then I get the records where EmployeeID = 1
This looks like you will need to use VBA to code round this limitation
CurrentDb.QueryDefs("MyQue
Cheers, Andrew
DECLARE @ProjectID int
Select * From Employees Where EmployeeID = 1
SET @ProjectID = 1
Select * From Employees Where EmployeeID = 2
ASKER
Thanks all. Although I was going to use VBA to set the '@Project = 1' I didn't want to have to re-write the complete sql statement as it is lengthy. But I guess there is no way as TextReport stated, Access is hitting the first select and going no further.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I didn't even think of that!!! Great suggestion.
set @ProjectID = 6233
Select * From Project Where ProjectID = @ProjectID