stored procedure

Question

stored procedure

khasim76

SSC Enthusiast

Points: 195
More actions
September 18, 2008 at 11:47 am

#193810

Create procedure usp_getInfo(@sql,@v_dt datetime, @o_company_name varchar(255)
As
Begin
declare @sql varchar(500)
@sql=Select 'conm' Into @o_company_name
From issuer c,eq_company b
Where C.issuer_id = b.company_key
And valid_to >= v_dt
And valid_from <= v_dt
order by issuer_id
EXEC ( @sql )
End

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

This will work better if you ask us a question.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

Even without a question I have some comments:

usp_getInfo(@sql,@v_dt datetime, @o_company_name varchar(255)

@sql

2. You have the parameter @sql and a variable in the body declared as @sql this doesn't work either.

3. I think you want @o_company_name to be declared as an output parameter.

4. Since you are using dynamic SQL the @o_company_name parameter will not longer exist as the dynamic SQL is executed in a different context.

5. In your dynamic sql statement you are using v_dt which does not exist, it needs to be @v_dt and just as I mention in point 4 it won't exist in the execution context of the dynamic SQL.

6. You are using dynamic SQL for no reason.

When you post a question I'll answer that.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 3

I don't know what you're trying to do, but I see several mistakes offhand:

1.) you open the parenthesis on the CREATE PROCEDURE but don't close them after the last parameter

2a.) you define @sql as a parameter with no datatype, it needs one

2b.) you define @sql as both a parameter and a variable inside the stored procedure

3.) you define a parameter o_company_name that looks like it should be an OUTPUT parameter becuase you're storing a value in it from the query

4.) you are hardcoding a query and trying to execute it as a dynamic query, but parameterized wrong?

5.) you were assigning a value to the variable @sql without using SET, and without putting quotes around it

6.) you are using the INTO clause, which creates a new table, not stores values to a variable

7.) it looks like your query could easily return more than one row, which may or may not give you the company name you expect depending on the values of ISSUER_ID

8.) if it's always the same query do you really need to pass it in and execute it dynamically?

If I were to take a guess at what you're trying to do, I'd write it like this, with the caveat that I don't understand the desired end result:

CREATE PROCEDURE usp_getInfo (

@v_dt datetime,

@o_company_name varchar(255) = NULL OUTPUT)

AS

BEGIN

SELECT @o_company_name = conm

FROM issuer c

INNER JOIN eq_company b ON c.issuer_id = b.company_key

WHERE valid_to >= @v_dt

AND valid_from <= @v_dt

ORDER BY c.issuer_id

END

oops, i see it took me so long to type all this someone else pretty much said the same stuff.

khasim76 SSC Enthusiast Points: 195 More actions · Answer 4

I AM SORRY I WANTED THE STORED PROCEDURE FOR TO GET EDITED.

I AM NEW TO STORED PROCEDURE.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

Chris Harshman (9/18/2008)
oops, i see it took me so long to type all this someone else pretty much said the same stuff.

Yeah, but you added some stuff I missed AND provided code. I thought about providing code, but decided I'd see what the OP had to say first.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question