Output from sp_exeutesql .. how ?

abdusalam · June 23rd, 2004, 03:20 AM

Hi,

    i am excuting sql string trhough stored proc
  sp_excutesql @sqlstring

    but i need output from sql string ..how can i get as i do with

    @result = (select Max(col) from mytbl where col1 like 'a%')

    how to get from

   exec sp_excutesql @sqlstring

please help

Stay Beautiful,

Abdul Salam

There is no moving creature on Earth whose sustenance is not provided by Allah. He knows its living and its resting place,
and all that is recorded in a glorious Book. (Al-Quran)

om_prakash · June 23rd, 2004, 04:55 AM

You have to use 'OUTPUT' parameter in your Stored Procedure

Om Prakash

happygv · June 23rd, 2004, 05:08 AM

Can you exlain in detail about what you are looking for?

Quote:

quote:but i need output from sql string

When you execute any sql statement, you would get the output, but not sure what you wanted to achieve.

Should your post look like this?
@result = (select Max(col) from mytbl where col1 like 'a%')
exec sp_excutesql @result

I am a bit confused.

_________________________
-Vijay G

Strive for Perfection

qazi_nomi · June 23rd, 2004, 05:33 AM

I think that he want to use a queery run in a stored procedure
then
@result = (select Max(col) from mytbl where col1 like 'a%')
exec sp_excutesql @result

else explain wat u want

Love 4 all

abdusalam · June 23rd, 2004, 08:27 AM

yes you are right happygv and qazi nomi

@result = (select Max(col) from mytbl where col1 like 'a%')
exec sp_excutesql @result

i dont know how to get output from sql server x-stored procedure sp_excutesql.......
and sql string ( @result) is based on user selections ...

Stay Beautiful,
Abdul Salam

happygv · June 23rd, 2004, 08:41 AM

Hi abdul,

Here it is.

Code:

Create Procedure DynamicResult (@result varchar(255))
as
    If @result is not NULL
        exec (@result)
    return

You can execute that procedure using

Code:

Execute DynamicResult 'Select * from YourTable'

Execute DynamicResult 'select Max(col) as MacCol from mytbl where col1 like ''a%'''

Hope that helps.
Cheers!

_________________________
-Vijay G

Strive for Perfection

abdusalam · June 23rd, 2004, 10:23 AM

Yes, thanks Happygv or Vijay G

        i need result in vairable .. that i could not write before .. sorry...

    as i use

        Select @MaxVal = Max(col) from mytbl
    how can i get result in @maxval from String ... okay i write whole problem code..

    Declare @SrtSQL nVarChar(4000)
    Declare @Rslt int
     Set @StrSQL = N'Select Max(Col) As MaxVal From MyTbl'
     Excute @Rslt = sp_excutesql @StrSQL

        Select @Rslt -- It return Zero ...

     I got maximum value after excution of string but when i check @Rslt it retun 0 ...WHY ?
       Becuase it return 0 that proc has succefully excuted ... but i need same Max value in @Rslt

      ..
Hope now you have gotten my acutal point ....

thanks
Stay Beautifull,
Abdul Salam

happygv · June 23rd, 2004, 11:02 AM

Hi Abdul,

That is because, after successful execution of any statement the SQL server variable @@ERROR would have the success or error status of the last statement executed. Anything other than ZERO indicates the error. In your case, it has been successfully executed, so you get a ZERO as @Rslt.

I wonder why you have to complicate things by having to execute a sql string, as it seems you are running it to get max(col), is that going to return the same max(col) except that the other factors might change? If not you don't have to go for executing dynamic sql string when the result is NOT going to be different each time.

But I dont think you can get there by the way you have mentioned.
Execute @Rslt = sp_excutesql @StrSQL

Instead you can use it as suggested by om_prakash in his post earlier, which I feel would be round the circle to achieve.

You can take a look at this page to understand how OUTPUT parameter works and fit that into your solution.
http://www.sqldts.com/?234

Hope that helps.
Cheers!

_________________________
-Vijay G

Strive for Perfection

abdusalam · June 24th, 2004, 12:53 AM

Hi Vijay,

   You and om_prakash are right as well BOL (Books online) too :)... I know how to get output from a stored procedure but there is no stored procedure ... that is UDF .. actually reason of creating UDF like this is that i have some ID Columns about each table that make new key vale against FK (forign key) i.e ( But doestn't matter that is UDF or PROC problem is same )

         Select @newid = Max(col) From tbl where FK = 1
           /*code that Generates new id according to key spacifications of table
             i mean every table is bounded with spacific numeric values....
             i.e tbl_1 id allowed between 20 and 30 etc
             so here i check table and then check max existing id value
             and then generate new key ....
           */

   i was doing it with writing code for each table (more than 30 lines code on each tabel)
    So i decide to reduce code and make it more readable through following way but still unable to get result.... hope now you can get story .... very clearly ...

Create Function GetID (@sql nvarchar(4000))
Returns Int
As

begin
declare @newid int

if (@sql IS NULL )
begin
   set @newid = NULL
   Return @newid
end
else
  exec @newid = sp_executesql @sql
  if (@@error = 0 And @newid is not null)
    begin
       /*
              Process on tables and linked tables.....
           */
  end
Return @newid
end

Stay Beautiful,
Abdul Salam

happygv · June 24th, 2004, 05:21 AM

Hi Abdul,

Then you can go for this instead of using a dynamic stored proc to get the max value.

Code:

Select coalesce(max(ID_column)+1,1) from TABLENAME

If the table have any rows it returns the max value + 1, else it returns 1 with which you can start with.

This gives you the new ID value with just a line of code. Does this help your need?

Cheers!

_________________________
-Vijay G

Strive for Perfection