getting different values from select statement

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

SQL Server 2005 Forums

Transact-SQL (2005)

getting different values from select statement

Author

Topic

chedderslam
Posting Yak Master

223 Posts

Posted - 2008-06-13 : 10:30:18

I am running a select statement against a function that is giving me different values depending on how it is called. When I run it through sql server management studio, I get the proper results, the default value column has the parameters default value. When I call it through my web app, I get this in the default value column:
*** ERROR OCCURRED level 2 (this is not a default value) ***

Why would the same sql statement get different results?

Here is the call:
select * from dbo.f_GetSProcParameters('webservices_BENEFICIAL_USES_DM_SELECT')

Here are the two functions:
---------------------------------------------------
USE [si_training_db]
GO
/****** Object: UserDefinedFunction [dbo].[f_GetSProcParameters] Script Date: 06/13/2008 09:29:21 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER function [dbo].[f_GetSProcParameters]( @StoredProcedureName VARCHAR(128) )

Returns @sProcParameters Table(ParmName VarChar(128),
DefaultValue VarChar(128),
HasDefault Bit,
IsInput Bit)

/*
* DESCRIPTION : This function returns a table listing all the parameters of a stored
* procedure and the default values of those parameters.
*
* RETURNS : table
*
*
* ORIG AUTHOR : Josh Kinder
* DATE WRITTEN : 3/14/2006
*
* REVISIONS LOG
*
* ID/Date PC# Description
* ------- --- ----------------------------------------------------------
*/

As

Begin

Declare
@Count SmallInt,
@Index SmallInt,
@CurParm VarChar(128),
@DefaultVal VarChar(128),
@IsInput BIT

/*----------------------------------------------------------------------------------------------------------------------------------*/
--EDIT CHECK -sProc only
/*----------------------------------------------------------------------------------------------------------------------------------*/

-- Check that the proc name is valid
If OBJECT_ID(@StoredProcedureName, 'P') Is Null
Begin
Goto ScriptErr
End

/*----------------------------------------------------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------------------------------------------------*/

Declare @ParmTable Table (Id SmallInt Identity Primary Key Clustered, ParmName VarChar(128))

Insert Into @ParmTable
Select
a.Name
From
SysColumns a
Inner Join SysObjects b On b.Id = a.Id
Where
b.Name = @StoredProcedureName

Select
@Count = Count(Id),
@Index = 1
From
@ParmTable

While (@Index<=@Count)
Begin

Select @CurParm = ParmName From @ParmTable Where Id = @Index

Set @DefaultVal = dbo.f_GetsProcParamDefaultValue(@StoredProcedureName,
@CurParm)

SELECT @IsInput = CASE WHEN params.is_output = 1 THEN 0 ELSE 1 END
FROM sys.procedures AS procs
INNER JOIN sys.all_parameters AS params ON params.object_id = procs.object_id
LEFT JOIN sys.types AS types ON types.system_type_id = params.system_type_id
AND types.user_type_id = params.user_type_id
WHERE procs.is_ms_shipped = 0
AND params.name = @CurParm
AND procs.name = @StoredProcedureName

Insert Into @sProcParameters
(
ParmName,
DefaultValue,
HasDefault,
IsInput
)
Values
(
@CurParm,
@DefaultVal,
Case When @DefaultVal = 'NoDefaultExists' Then 0 Else 1 End,
@IsInput
)

Set @Index = @Index + 1
End

ScriptErr:
Return

End

---------------------------------------------------
USE [si_training_db]
GO
/****** Object: UserDefinedFunction [dbo].[f_GetsProcParamDefaultValue] Script Date: 06/13/2008 09:30:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER function [dbo].[f_GetsProcParamDefaultValue]
(
@StoredProcedureName VarChar(128),
@ParamName VarChar(128)
)

Returns VarChar(128)

/*
* DESCRIPTION : This function returns a table listing all the parameters of a stored
* procedure and the default values of those parameters.
*
The following copyright info is for the parsing algorithm to get the default value.
I obtained the code from SQL Farms Solutions and their only stipulation for use is
that the copyright info remain with the code. Although I customized it for us, it is
still their algorithm.

==================================================================================
Copyright © SQL Farms Solutions, www.sqlfarms.com. All rights reserved.
This code may be used at no charge as long as this copyright notice is not removed.
==================================================================================

* RETURNS : table
*
*
* ORIG AUTHOR : Josh Kinder
* DATE WRITTEN : 3/14/2006
*
* REVISIONS LOG
*
* ID/Date PC# Description
* ------- --- ----------------------------------------------------------
*/

As

Begin

Declare
@minRow Int,
@maxRow Int,
@tmpInt Int,
@tmpText VarChar(4000),
@tmpCharPos1 Int,
@tmpCharPos2 Int,
@tmpCharPos3 Int,
@ParameterDefault VarChar(128),
@sProcText VarChar(4000),
@Delimiter Char(1),
@Count SmallInt,
@Index SmallInt,
@CurText VarChar(4000)

Select
@minRow = Null,
@maxRow = Null,
@Delimiter = char(13)

Declare @ProcText Table (Id Int Identity(1, 1) Primary Key Clustered,
ProcText VarChar(4000))

Insert Into @ProcText
Select
a.Text
From
SysComments a
Inner Join SysObjects b On b.Id = a.Id
Where
b.Name = @StoredProcedureName

Select
@Count = Count(Id),
@Index = 1
From
@ProcText

While (@Index<=@Count)
Begin

Select @CurText = Coalesce(@CurText,'') + ProcText From @ProcText Where Id = @Index

Set @Index = @Index + 1

End

/*----------------------------------------------------------------------------------------------------------------------------------*/
--EDIT CHECKS
/*----------------------------------------------------------------------------------------------------------------------------------*/

-- Check that the parameter name is valid for the proc
If Not Exists(
Select
1
From
INFORMATION_SCHEMA.PARAMETERS
Where
SPECIFIC_NAME = @StoredProcedureName
And PARAMETER_NAME = @ParamName
)
Begin
Set @ParameterDefault = '*** ERROR OCCURRED level 1 (this is not a default value) ***'
Goto ScriptErr
End

/*----------------------------------------------------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------------------------------------------------*/

/*----------------------------------------------------------------------------------------------------------------------------------*/
--Get sProc into a workable temporary table
/*----------------------------------------------------------------------------------------------------------------------------------*/

Declare @ProcContent Table (Idx Int Identity(1, 1) Primary Key Clustered,
ProcText VarChar(4000))

Insert Into @ProcContent
Select Value From dbo.f_Split(@CurText, @Delimiter, 1, 0)

-- Make sure that some rows were returned successfully
If @@ROWCOUNT = 0
Begin
Set @ParameterDefault = '*** ERROR OCCURRED level 2 (this is not a default value) ***'
Goto ScriptErr
End

/*----------------------------------------------------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------------------------------------------------*/

/*----------------------------------------------------------------------------------------------------------------------------------*/
--Get location of parm and get ready to parse
/*----------------------------------------------------------------------------------------------------------------------------------*/

-- Get the first line where the parameter is referenced in the proc code.
-- (the LIKE here is a little complex since it is possible that multiple parameters
-- will start with the same string. Most cases of interest are covered by the
-- conditions listed below).
Select
@minRow = Min(Idx)
From
@ProcContent
Where
ProcText Like '%' + @ParamName + ' %'
Or ProcText Like '%' + @ParamName + Char(9) + '%'
Or ProcText Like '%' + @ParamName + Char(10) + '%'
Or ProcText Like '%' + @ParamName + Char(13) + '%'
Or ProcText Like '%' + @ParamName + '=%'
Or ProcText Like '%' + @ParamName + '%=%'
Or ProcText Like '%' + @ParamName + ',%'

-- Check that the parameter is referenced in the code
If @minRow Is Null
Begin
Set @ParameterDefault = '*** ERROR OCCURRED level 3 (this is not a default value) ***'
Goto ScriptErr
End

-- Get the proc line where the word 'AS' is declared. 'AS' is required
-- upon proc creation to complete the variable declaration.
-- Note: This cover most cases of interest. There could be scenarios where
-- additional condition should be applied.
Select
@maxRow = Min(Idx)
From
@ProcContent
Where
ProcText Like '% AS'
Or ProcText Like '% AS '
Or ProcText Like '% AS' + Char(9)
Or ProcText Like '% AS' + Char(10)
Or ProcText Like '% AS' + Char(13)
Or ProcText Like 'AS %'
Or Upper(RTrim(LTrim(ProcText))) = Char(10) + 'AS'
Or Upper(RTrim(LTrim(ProcText))) = 'AS'
Or Upper(RTrim(LTrim(ProcText))) = 'AS' + Char(10)
Or Upper(RTrim(LTrim(ProcText))) = 'AS' + Char(13)
Or Upper(RTrim(LTrim(ProcText))) = 'AS' + Char(13) + Char(10)
Or Upper(RTrim(LTrim(ProcText))) = 'AS' + Char(10) + Char(13)

-- Check that the 'AS' string was found successfully
If @maxRow Is Null
Begin
Set @ParameterDefault = '*** ERROR OCCURRED level 4 (this is not a default value) ***'
Goto ScriptErr
End

/*----------------------------------------------------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------------------------------------------------*/

/*----------------------------------------------------------------------------------------------------------------------------------*/
--Parse and get the default value
/*----------------------------------------------------------------------------------------------------------------------------------*/

-- Get the first proc line of code where the parameter is referenced, for string processing,
-- and append to it all proc rows until the 'AS' string
Select
@tmpText = LTrim(RTrim(Replace(Replace(ProcText, Char(10), ''), Char(13), ''))) + ' '
From
@ProcContent
Where
Idx = @minRow

While @minRow < @maxRow
Begin
Set @minRow = @minRow + 1

Select
@tmpText = @tmpText + ' ' + LTrim(RTrim(Replace(Replace(ProcText, Char(10), ''), Char(13), ''))) + ' '
From
@ProcContent
Where
Idx = @minRow
End

-- Find the position of the parameter name. Delete all text before that position.
Set @tmpInt = Null
Set @tmpInt = PatIndex('%' + @ParamName + '%', @tmpText) - 1
Set @tmpText = Right(@tmpText, Len(@tmpText) - @tmpInt)

-- At this point we are nearly done:
-- We check whether the character '=' comes before the 'AS' or ',' string
-- If not- the parameter has no default value.
-- If so, we continue to find the value of the default parameter

Set @tmpCharPos1 = PatIndex('%=%', @tmpText)
Set @tmpCharPos2 = PatIndex('%,%', @tmpText)
Set @tmpCharPos3 = PatIndex('% AS %', @tmpText)

If @tmpCharPos1 <= 0
Or (@tmpCharPos1 > @tmpCharPos2 And @tmpCharPos2 > 0)
Or (@tmpCharPos1 > @tmpCharPos3 AND @tmpCharPos3 > 0)
Begin
-- The column does not have a default
Set @ParameterDefault = 'NoDefaultExists'
End
Else
Begin
-- Column has a default and it is left to find it.
-- First chop the string until the '=' character
Set @tmpInt = NULL
Set @tmpInt = PatIndex('%=%', @tmpText) - 1
Set @tmpText = LTrim(Right(@tmpText, Len(@tmpText) - @tmpInt))

-- Now, we p*** the remaining string until we get a ',' or a ' ' character
Set @tmpCharPos1 = NULL
Set @tmpCharPos2 = NULL

Set @tmpCharPos1 = PatIndex('%,%', @tmpText)
Set @tmpCharPos2 = PatIndex('% %', @tmpText)

Set @tmpInt = NULL

If @tmpCharPos2 > @tmpCharPos1 AND @tmpCharPos1 > 0
Set @tmpInt = @tmpCharPos1 - 1
Else
Set @tmpInt = @tmpCharPos2 - 1

If @tmpInt <= 0
Begin
Set @ParameterDefault = '*** ERROR OCCURRED level 5 (this is not a default value) ***'
Goto ScriptErr
End

Set @ParameterDefault = SubString(@tmpText, 1, @tmpInt)

-- If the parameter default is a string, then we will have an '' at each side of it.
-- These last lines of code will get rid of the ''.
If Len(@ParameterDefault) >= 1
If Right(@ParameterDefault, 1) = ''''
Set @ParameterDefault = Left(@ParameterDefault, Len(@ParameterDefault) - 1)

If Len(@ParameterDefault) >= 1
If Left(@ParameterDefault, 1) = ''''
Set @ParameterDefault = Right(@ParameterDefault, Len(@ParameterDefault) - 1)
End

/*----------------------------------------------------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------------------------------------------------*/

Goto ScriptExit

ScriptErr:

ScriptExit:

Return @ParameterDefault

End

chedderslam
Posting Yak Master

223 Posts

Posted - 2008-06-13 : 15:11:31

Some more information:

If I change the web app to use the SA login, the same one I am using for SQL Server Management Studio, I get the proper query results, si I guess it has something to do with permissions. What should I be looking for to fix on the permissions side?

I added the public role and gave it either select for the table value function or execute for the scalar value functions. That didn't help. So it has to do with the logins but I don't know where to look.

Thanks,
Matthew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources