Jump to content
  • Advertisement
Sign in to follow this  
CyberSlag5k

[web] SQL - Storing the results of a stored procedure within a function

This topic is 4162 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

I have a bit of a dilemma. I want to use the results of a stored procedure within a function, but to do that I need to store the results in a temporary table. Since I can't do that within a function, I'm kind of stumped. Basically all I'm looking to do is write a function that returns 1 if the stored proc returned 1 or more rows, and 0 if it doesn't:
CREATE FUNCTION ISPRIMARY
(
@table nvarchar(100),
@column nvarchar(100)
)
RETURNS bit
AS
BEGIN
create table #primaries (table_qualifier sysname, table_owner sysname, table_name sysname, 
column_name sysname, key_seq smallint, pk_name sysname)

insert into #primaries
exec sp_pkeys @table

declare @count int
select @count = count(*) from #primaries where column_name = @column

if @count > 0
return 1
else
return 0

As you can see, at the higher level, I'm passing in a table name and a column name, and want to return true (1) if the column is a primary key for the table. I'm not having very much luck, though. I don't think I can use a table variable to store the results of the stored proc (though if I can I think that'd work just fine). Any suggestions?

Share this post


Link to post
Share on other sites
Advertisement
1) You may get away with using the @@ROWCOUNT to find the number of rows. But you will have to be careful that the row count variable is not affected by another query.
2) You could have the stored procedure itself return the number of rows, by using the RETURN statement:
CREATE PROCEDURE MyProcedure
(
/* ..parameters.. */
)

/* do stuff */

RETURN CASE @@ROWCOUNT WHEN 0 THEN 0 ELSE 1 END /* or use a count function somewhere, whatever floats your boat */

GO

---------------------------------------------

USAGE:

DECLARE @num int

EXEC @num = MyProcedure /* ..parameters.. */




<hr/>

EDIT:

Quote:
Original post by CyberSlag5kAs you can see, at the higher level, I'm passing in a table name and a column name, and want to return true (1) if the column is a primary key for the table.

I am assuming you're using MSSQL Server: look at the script in the sp_columns procedure (or possibly other procedures) in the master database. By stripping that down to create your own stored procedure, you should be able to create a much more elegant solution.

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

We are the game development community.

Whether you are an indie, hobbyist, AAA developer, or just trying to learn, GameDev.net is the place for you to learn, share, and connect with the games industry. Learn more About Us or sign up!

Sign me up!