Newbie question

[fluxdemon]

I am rather new to SQL server and writing stored procedures for it. Why does it require redundant declaration? E.G... the declared variables must be matched up with the fields of the table in the select statement and then in the conditional (set) statements used to get the final result.

 

[mrdenny]

I'm not sure what you are referring to. Please provide an some example code.

Variable declaration is completly optional, and is only needed when you are going to use the variables.

Denny

 

[SonOfEmidec1100]

I agree, it sounds as if the declarations are redundant in which case you dont need them. You must be able to simplify your SP.

If the sp is simply returning a recordset all you need is

SELECT field1,field2,field3 FROM MyTable
OR to select all fields
SELECT * FROM MyTable

Any Help ?

 

[SQLSister]

We really need to see some code to figure out what you are doing. I never need to declare my variables more than once in the same stored procedure.

 

[fluxdemon]

Sorry. The problems I'm working with are schoolwork but here is a example:
procedure sppatron_info
(
@ssn_in char(9),
@name_out char(30) output,
@number_of_books_out numeric(4) output,
@number_overdue_out numeric(4) output,
@total_fine_out char(10) output,
@return_message char(200) output,
@return_sqlcode numeric(4) output
)
as
begin

declare gc_sppatron_info cursor global for
select
@ssn_in=c.ssn,
@name_out=p.name,
@number_of_books_out = count(c.scan_code),
@number_overdue_out = count(datediff(day,c.date_due,(select todays_date from currentdate))),
@total_fine_out = '$' + convert(char(10), sum(datediff(day,c.date_due,(select todays_date from currentdate))*.15),
@return_message = null,
@return_sqlcode = null

from
circ c,
collection n,
dp260i..patron p

where n.scan_code = c.scan_code
and c.ssn = p.ssn

open gc_sppatron_info

if @@RowCount=0
begin
set @return_sqlcode = 100
set @return_message = 'Patron #' + @ssn_in + ' Not Found'
set @ssn_in=null
set @name_out=null
set @number_of_books_out=null
set @number_overdue_out=null
set @total_fine_out=null
set @return_message=null
set @return_sqlcode=null

end
else
begin
set @return_sqlcode = 0
set @return_message = 'Patron #' + @ssn_in + ' Found'
end

return @return_sqlcode

end

 

[SQLBill]

I only see ONE place where the variables are being DECLAREd.

I see TWO places where the variables are being SET. But that's because of two different requirements:

1. First the SELECT inputs data into the variable.
2. Then if certain criteria is met (@@ROWCOUNT=0) then the variables are reSET to something else.

-SQLBill