Another stupid question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Another stupid question

Author

Topic

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-08-11 : 07:10:37

I now have:

ALTER procedure [dbo].[CheckIfLogExists]

@Month int,
@Year int,
@exists bit output

as

Set @exists = 0

if exists(
select
LogID
From
LogTable
where
MONTH(LogDate) = @Month
AND
YEAR(LogDate) = @Year
)
set @exists=1

return @Exists

And this seems to work, only when I run it from the website, it says:

Parameter count does not match Parameter Value count

I'm putting in month and year, and expect exists as output, but perhaps the sp also expects something for existst as input?
What should I do

georgev
Posting Yak Master

122 Posts

Posted - 2008-08-11 : 07:14:37

Try
[CODE]
@exists bit = 0 output
[/CODE]

George

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-11 : 07:15:56

You need to provide a variable for it to put the results into.

Call it with

EXEC [dbo].[CheckIfLogExists]
@Month = 10
@Year = 2008
@exists = @doesItExist OUT

-------------
Charlie

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-11 : 07:16:03

[code]declare @exists int

exec CheckIfLogExists @Month = 8, @Year = 2008, @exists = @exists OUTPUT[/code]

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-11 : 07:16:41

KH
[spoiler]Time is always against us[/spoiler]

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-11 : 07:19:16

Khtan,

What's the rule about your yak sniper picture? You don't post it every time....

-------------
Charlie

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-08-11 : 07:20:45

Right....

these are the things that make SQL a puzzle to me...
If I define something as OUTPUT, I would not expect that I would have to INPUT a value....
But then, who am I....

The secret to creativity is knowing how to hide your sources. (Einstein)

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-08-11 : 07:24:23

I am calling: DataProvider.Instance().ExecuteScalar("CheckIfLogExists ", Month, Year)

From code, so what would I need to change?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-11 : 07:41:41

quote:
Originally posted by Transact Charlie

Khtan,

What's the rule about your yak sniper picture? You don't post it every time....

-------------
Charlie

A similar solution was posted, while i was posting mine

KH
[spoiler]Time is always against us[/spoiler]

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-08-11 : 07:41:52

I now have:

ALTER procedure [dbo].[CheckIfLogExists]

@Month int,
@Year int,
@exists bit = 0 output

as

if exists(
select
LogID
From
LogTable
where
MONTH(LogDate) = @Month
AND
YEAR(LogDate) = @Year
)
set @exists=1

return @Exists

And it works when I call it from Management Studio like this:

USE [npz]
GO

DECLARE @return_value int,
@exists bit

EXEC @return_value = [dbo].[CheckIfLogExists]
@Month = 8,
@Year= 2008

SELECT @exists as N'@exists'

SELECT 'Return Value' = @return_value

GO

But when I run from the application, it still says

Parameter count does not match Parameter Value count.

The secret to creativity is knowing how to hide your sources. (Einstein)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-11 : 07:43:53

[code]USE [npz]
GO

DECLARE @return_value int,
@exists bit

EXEC @return_value = [dbo].[CheckIfLogExists]
@Month = 8,
@Year= 2008,
@exists = @exists OUTPUT

SELECT @exists as N'@exists'

SELECT 'Return Value' = @return_value

GO[/code]

KH
[spoiler]Time is always against us[/spoiler]

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-11 : 07:44:02

I think you should consider rewriting your stored proc as a function that returns a bit. You code example is a prime candidate for this as it doesn't modify the state of the db and doesn't require dynamic sql.

When you declare the sp with an output variable and want to get the value of that you need to provide it with a container variable with the output parameter.

-------------
Charlie

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-08-11 : 07:49:18

quote:

Originally posted by khtan

USE [npz]
GO

DECLARE @return_value int,
        @exists bit

EXEC @return_value = [dbo].[CheckIfLogExists]
     @Month = 8,
     @Year= 2008,
@exists = @exists OUTPUT 

SELECT @exists as N'@exists'

SELECT 'Return Value' = @return_value

GO

KH
[spoiler]Time is always against us[/spoiler]

Yes, I know this also works, but that's not my point.
The point is, I am firing this code from my application:

DataProvider.Instance().ExecuteScalar("CheckIfLogExists", Month, Year)

And it still gives me the "Parameter count does not match Parameter Value count." error.

So, what should I do:
1) something like:

DataProvider.Instance().ExecuteScalar("CheckIfLogExists", Month, Year, 0)

2) change the SP into something which does not ask for another parameter

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-11 : 07:52:23

Something like,


CREATE FUNCTION [dbo].[CheckIfLogExists] (
	@Month INT
	, @Year INT
	)
	RETURNS BIT
AS BEGIN

DECLARE @exists BIT
SET @exists = 0

IF EXISTS (
	SELECT
		LogID
	FROM
		LogTable
	WHERE
		MONTH(LogDate) = @Month
		AND
		YEAR(LogDate) = @Year
	)
	SET @exists = 1

RETURN @Exists
END

And then call it in whatever programming language you are using with the relevant syntax to call a function whatever that is.

Alternatively, If you are coding in the way that I think you are coding.

ExecuteScaler gets the 1st row of the RESULT SET of your called object. Change your stored proc to SELECT the result rather than RETURN it. If that works you can remove the @exists OUTPUT because it's not necessary.

Which

-------------
Charlie

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-11 : 07:53:11

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar.aspx

-------------
Charlie

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-11 : 08:03:53

You *can* make your code work in the following way.

Set your stored procedure to this...


ALTER PROCEDURE [dbo].[CheckIfLogExists]

	@Month int,
	@Year int

AS BEGIN

DECLARE @result BIT
SET @result = 0

IF EXISTS (
	SELECT
		LogID
	FROM
		LogTable
	WHERE
		MONTH(LogDate) = @Month
		AND
		YEAR(LogDate) = @Year
	)
	SET @result = 1

SELECT @result
END

And call it with your application like this.
DataProvider.Instance().ExecuteScalar("CheckIfLogExists", Month, Year)

And I think it will work.

HOWEVER,

See my comments on Functions because this stored proc should be one.

-------------
Charlie

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-08-11 : 08:06:54

Now that looks like a nice solution Charlie.
Thanks!!!

The secret to creativity is knowing how to hide your sources. (Einstein)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources