Function with dinamic query returning a value

[camaleonCHILE]

Hi dear friends !!!

I need obtain a return value from a function. In this function, I work with a query built dynamically, from which I get the value. So the thing is:

DECLARE @X INTEGER
DECLARE @STRING NVARCHAR(4000)

SET @STRING='SELECT COUNT(*) FROM MY_TABLE'

EXECUTE(@STRING)

i need to put the count(*) value in the @X variable and return it.

Thanks in advance

 

[vongrunt]

Considering above example... why dynamic?

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[camaleonCHILE]

because I've to add some business rules to that query,
something like this:


SET @STRING='SELECT COUNT(*) FROM MY_TABLE '

IF @PARAMETER=1
SET @STRING=@STRING + 'WHERE FIELD1 IS NULL'
ELSE
SET @STRING=@STRING + 'WHERE FIELD1 IS NOT NULL'


thanks for answer me

Regards

 

[SQLDenis]

Take a look at this

http://sqlservercode.blogspot.com/2006/03/use-spexecutesql-or-exec-to-get-count.html

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

Is that the extent of the business rule? Are there more rules?

If that's it, then you could do this...

Declare @AllCount Integer
Declare @NullCount Integer
Declare @NotNullCount Integer

Select  @AllCount = Count(1),
        @NotNullCount = Count(Field1),
        @NullCount = Count(1) - Count(Field1)
From    MY_TABLE

If @PARAMETER=1
    Set @X = @NullCount
Else
    Set @X = @NotNullCount

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom