Stored Procedure Question - Conditional Return Value?

IF EXISTS(SELECT [ValueID] FROM Settings WHERE BankID = @BankID AND SectionID = @SectionID AND KeyID = @KeyID AND [ValueID] IS NOT NULL)


Why not?

I had a problem overcoming syntax errors if not first setting up the query string then calling the query through the EXEC command. It did not like assignments as shown above in the SELECT clause's argument list. Any suggestions?

If it will not help U please specify what exactly U want to achieve,what syntax errors you trying to overcome and why U don't do it in standard way .

Best regards,
Krzemo.

I would be more than happy to learn to do this in a "standard way". Although I have years of non-database experience in MFC and C++, SQL Server and stored procedures are relatively new to me.

If, by "standard", you are referring to the example in the first reply to this post, I tried that. It does not have any syntax errors, yet it also does not return the value from the SELECT statement, when using Query Analyzer.

I was shure that U want only to have return value set. :)
IF U want ouput of that value in Query Analizer U must put this code:


DECLARE @Stat INT
EXEC @Stat=sp_get_setting ..params here..
SELECT @Stat Status

It works also with my code in first post.

By the way, Isn't it better to check in application if any rows has returned?
Why U want SQL to do this check for U.

Best regards,
Krzemo.

Hmmm, two things: the return value for [ValueID] is a string of 255 characters, not an INT. I am not sure the solution above (DECLARE @Stat INT...) will provide me with the info I am looking for.

As for your second question, here is a brief summary of what this stored proc is needed for:

sp_get_setting retrieves application settings from a table. These "settings" are like those you might get from the Registry or an INI file to initialize application variables upon startup, and throughout the use of the application. An example might be a path like: "C:\Program Files\C&A Associates\ProofPC\Images". This "setting" provides the application with "where" to save images acquired by the scanner.

This application initially was using Access as its database, as the application had few users and did not need a robust solution such as SQL Server. This application was also designed with the "settings" built into a database table (as opposed to a local INI file or in Registry settings). In addition to multiple users, there is a lot of database traffic in just retrieving these values (although there is a cache feature that helps somewhat). The goal has been to quickly get SQL Server integrated to increase performance with a greater number of users, and make its data transaction more efficient.

For the Access DB, all SQL calls where direct (no QueryDefs, etc). A noticeable increase in retrieving these settings was observed even with my initial attempt with my version of the stored procedure.

When using Access, the application would retrieve each "setting" with the following code:

query.Format("SELECT [" + cstrValue + "] FROM Settings WHERE BankID='%s' AND " + cstrSection + "='%s' AND " + cstrKey + "='%s'", m_bankID, section, key);

CODBCRecordset recordset(&m_database);

TRY recordset.Open(query, CRecordset::snapshot);

It's attempt to see if a "setting" record was found was performed with the following code:
if (! recordset.IsEOF())
{
val = recordset.Field(cstrValue);
}
else
{
val = def;
}

recordset.Close();

In other words, if a value was not found, the "setting" we were looking for, which gets set to the variable "val" gets set to a default value. Although this works fine, I would like to reduce the C++ code, and take use of both the return value from the table and the return value from the success (or failure) of the attempted query. I can do this with other methods, but would like to learn and understand the use and efficiency by using stored procedures. I also like the idea of localizing this functionality in the DB for reuse in other applications, simplifying their future development.

So U need procedure with default settings?

look here:

version with output parameter (no result set):

CREATE PROCEDURE dbo.sp_get_setting
@BankID char(4)=''
,@SectionID varchar(50)=''
,@KeyID varchar(50)=''
,@Value varchar(255) OUTPUT

AS
SELECT @Value=ValueColumn
FROM Settings
WHERE BankID = @BankID AND SectionID = @SectionID AND KeyID = @KeyID AND ValueColumn IS NOT NULL

IF @Value IS NULL SET @Value='Default Value'

RETURN 0


version with result set (1 row 1 value)

CREATE PROCEDURE dbo.sp_get_setting
@BankID char(4)=''
,@SectionID varchar(50)=''
,@KeyID varchar(50)=''
AS

DECLARE @Value varchar(255)
SELECT @Value=ValueColumn
FROM Settings
WHERE BankID = @BankID AND SectionID = @SectionID AND KeyID = @KeyID AND ValueColumn IS NOT NULL

IF @Value IS NULL SET @Value='Default Value'

SELECT @Value ValueReturned
RETURN 0


version with function:

CREATE FUNCTION dbo.sp_get_setting
( @BankID char(4)=''
,@SectionID varchar(50)=''
,@KeyID varchar(50)=''
) RETURNS VARCHAR(255)
AS
BEGIN
DECLARE @Value varchar(255)

SELECT @Value=ValueColumn
FROM Settings
WHERE BankID = @BankID AND SectionID = @SectionID AND KeyID = @KeyID AND ValueColumn IS NOT NULL
IF @Value IS NULL SET @Value='Default Value'
RETURN @Value
END

As U see "setting" is returned (in result set or output parameter) always. If there is no valid (not null) parameter than default value is returned.

Best regards,
Krzemo.