CODEWITHSUNDEEP
sqlsql-servert-sql
user2328256
user2328256

Reputation: 3

Why is my stored procedure not receiving its parameter?

I have following stored procedure:

create procedure new (@user nvarchar(50))
as
Declare @ids uniqueidentifier  
set @ids = (
select id from table6 where name = @user and @ids = id)

SELECT  * from table1 as 1, table2 as 2
where 1.id = @ids

It's not returning the correct results - it's returning nothing. It seems that it is passing the variable (@ids) as empty.

Upvotes: 0

Views: 73

Answers (2)

D Stanley
D Stanley

Reputation: 152556

You're not passing @ids - you're declaring it locally. Since it has no value, when you use in in your WHERE clause you get no records back, so @ids will be NULL.

I think you want

set @ids = (
select id from table6 where name = @user)

But you don't even need that - just do:

SELECT  * from table1 t1, table2 t2
where t1.id = (select id from table6 where name = @user)

Which will do a CROSS JOIN returning every combination of records from T1 and T2. - that may be what you want, just wanted to be sure to point that out.

Upvotes: 0

Guffa
Guffa

Reputation: 700322

The reason that you get an empty result is that you are trying to use @ids before you have assigned anything to it. In the query where you get the value for @ids you are using it to filter out records where @ids = id, but as @ids is null at that time the result will be empty and @ids will remain null.

I assume that you just want to remove that part of the condition, unless you have some other value that you can use to compare the id field to.

Anyhow, I don't see how you could even create the procedure... You can't use a number as an alias, use an identifier:

SELECT * from table1 as t1, table2 as t2
where t1.id = @ids

Upvotes: 1

Related Questions