August 1, 2006 at 10:43 am
i'm trying to declare a stored procedure and i'm
not able to do so... what am i doing wrong?
declare @block varchar (8000)
set @block = sp_blocky
doesn't work though... why?
any ideas?
_________________________
August 1, 2006 at 11:01 am
You're declaring a variable but the question is are you assigning the return value of sp_blocky to it, are you assigning the variable the string value 'sp_blocky'? How are you trying to then use this variable?
August 1, 2006 at 11:07 am
actually sp_blocky produces a report, and what I'm trying to
do is use the sp instead of putting the complex select statement
after the @block
then all i need to do is use the @block instead of using the select.
know what i mean?
eventually i would like the output from @block to go to into a file
some where, but haven't figured that part out yet.
all this executed from a job step which... haven' done yet. i'm in
the very beginning here actually.
thanks in advance for your help!
_________________________
August 1, 2006 at 11:19 am
Okay, I think I get where you're going... A few more questions:
The data from sp_blocky, is it a string, tabular data, or what?
If it returns tabular data what you want to do is declare a temporary table or table variable with the same definition as the output. So you might do something like this:
---------------------------------
DECLARE @block_output TABLE (
column1 INT
, column2 VARCHAR ( 25 )
, column3 VARCHAR ( 20 )
)
INSERT @block_output
EXEC sp_blocky
---------------------------------
Then you should be able to do whatever you want with the table data for the rest of whatever you're working on.
The other option is that you might want to consider rewriting sp_blocky as a function or view (if it's just a complex join you don't want to have to rewrite). That way you can reference it any time.
August 1, 2006 at 11:28 am
aaron... many thanks for your help my friend.
i'm pretty sure you understand exactly what i'm trying to do.
sp_blocky really is just a select statement which returns 3 values
so all i would need to do is the following:
DECLARE @block_output TABLE (
column1 INT
, column2 VARCHAR ( 50 )
, column3 VARCHAR ( 50 )
, column4 VARCHAR ( 50 )
)
then...
INSERT @block_output
exec sp_blocky
ok.. no problem so far... i'm going to try this out real quick,
but what about getting the actual results from @block_output
into say for example a .txt or some thing.
what next?
i'm going to try this stuff out now.
thanks again by the way.
_________________________
August 1, 2006 at 11:50 am
No problem man, I've had a lot of people answer a lot of my questions over the years.
If you need to get it out to a text file you've got a couple of different options. The most commonly used method I've seen is drop it into a global temporary table (CREATE TABLE ##tmp_table...) and use xp_cmdshell to call bcp out. I'd recommend against that as xp_cmdshell really isn't something you should learn to depend on. It's kind of a bad habit in my opinion.
If I have to get data like this out to a file, I like to wrap all the SQL up in an SSIS or DTS package and dump my results into a table and use that to write to a file. That might be more or less difficult for you depending on your level of experience. You're going to have to figure out what is best for you.
One good method would be to consider picking up Ken Henderson's The Guru's Guide to Transact-SQL (that's the second time I've referenced that book in the last hour on these forums) and checking out his sp_exporttable on page 476. The book is pretty much one of the best books you can have on SQL Server and his code there should do exactly what you're looking for.
August 1, 2006 at 11:58 am
excellent... thanks bro!
_________________________
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply