iterating through a multi-value parameter

is there a way to loop through a multi-value parameter and retrieve contents
of all selected values to dislpay on a report. currently, i can reference
individual parameter values by using Parameters!PARAMETER.Value(0...).
However with dynamic parameter values generated from a query, this becomes a
pain
regardsnitz
Create a text box and put in the following:
=Join(Parameters!MyParam.Label, ", ")
This should grab all of the parameters selected and display them in your
textbox. You also have to add the parameter to the query as well; but it
sounds like you have already done that. For example:
and (m.company_num IN (@.MyParam))
Hope this helps.
Rob Cuscaden
"nitz" wrote:
> is there a way to loop through a multi-value parameter and retrieve contents
> of all selected values to dislpay on a report. currently, i can reference
> individual parameter values by using Parameters!PARAMETER.Value(0...).
> However with dynamic parameter values generated from a query, this becomes a
> pain
> regards
>|||duh...thank you very much..i really need to review what text functions are
available..
since i have you on the line ;)..is there a way to loop through the
parameters collections grab paramaters and their values and output to a text
box...it would be nice to have reusable bit of code for every report.
instead of this
= "DIVISION: " & Parameters!DIVISION.Value & " BOUTIQUES: " &
JOIN(Parameters!REPS.Label, ", ") & VbCrLf & etc
thanks again
"Rob" wrote:
> nitz
> Create a text box and put in the following:
> =Join(Parameters!MyParam.Label, ", ")
> This should grab all of the parameters selected and display them in your
> textbox. You also have to add the parameter to the query as well; but it
> sounds like you have already done that. For example:
> and (m.company_num IN (@.MyParam))
> Hope this helps.
> Rob Cuscaden
> "nitz" wrote:
> > is there a way to loop through a multi-value parameter and retrieve contents
> > of all selected values to dislpay on a report. currently, i can reference
> > individual parameter values by using Parameters!PARAMETER.Value(0...).
> > However with dynamic parameter values generated from a query, this becomes a
> > pain
> > regards
> >sql

Issues with Parameterised Report

Hi,

I want a matrix report which displays Sales Data or Discount Data (Column) Vs Year (Row). I want to have a parameter which displays a text as 'Sales', 'Discount'.

When I Select 'Sales', the report should display 'Sales Amount' Vs 'Year' and when I select 'Discount', the report should display 'Discount Data' Vs Year. I am getting all the data from OLAP Cube.

Any help is highly appreciated.

Thanks,

S Suresh

Create a string type parameter (say Parameter1) and have Available Values to be "Sales" and "Discount" (Non-queried)

In your matrix grouping (row or column), type the following expression:

IIf(Parameters!Parameter1.Value="Sales", Fields!Sales.Value, Fields!Discount.Value)

Shyam

Issues passing Table Name as a parameter to a Stored Procedure

Hi Friends,

I use the stored procedure pr_Set_Min_Max_Ind to set some indicators in certain type of tables in my databases.

CREATE Procedure [dbo].[pr_Set_Min_Max_Ind] ( @.t SysName, @.k1 SysName, @.k2 SysName, @.r1 SysName, @.r2 SysName )
................
................
...
...
...
................
End

The procedure pr_Set_Min_Max_Ind takes five parameters ( @.t, @.k1, @.k2, @.r1, @.r2 ) of which
@.t is the table name in which I want to set some indicators and
the rest of the parameters (@.k1, @.k2, @.r1, @.r2) are column names in the table @.t

Say, I have this procedure stored in database A. Then to execute the procedure on table TestTable_A in database A , I write the statement as:

exec pr_Set_Min_Max_Ind TestTable_A, Column1, Column2, Column3, Column4 (statement-1)

Now, if I want to execute this procedure on a table TestTable_B in a different database B, I am NOT able to issue a statement like:

exec pr_Set_Min_Max_Ind B.dbo.TestTable_B, Column1, Column2, Column3, Column4 (statement-2)

The only way I now know to accomplish this is to save a copy of this procedure in database B and write a statement similar to statement-1. But, for some reasons I DO NOT want to do that. I just want to have the procedure stored in only one database and be able to pass the table names from other databases in the format database_name.dbo.table_name ;So, guys please let me know how I can pass the table name in the format shown as in statement-2 above.

Thanks in advance for taking time to give me a solution.

With Best Regards,

-Ram.

Depends on how you create your SQL statements based on the table names and column names that you pass to your stored procedure but you should be able to do a SELECT statement from another database in the same SQL Server. Try posting your SP so that we will be able to see where the problem could be.

SQL Server Helper
http://www.sql-server-helper.com

|||

Hi,

The following is the SP that I was referring to.

Thank you.

--

CREATE Procedure [dbo].[pr_Set_Min_Max_Ind](@.t SysName, @.k1 SysName, @.k2 SysName, @.r1 SysName, @.r2 SysName)

As

Declare @.SQL Varchar(8000),

@.x SysName

Set @.x = '##_' + @.t + '_' + Replace(Convert(Char,GetDate(),114),':','')

Begin

-- Tables which have a Min_Ind column

IF ((Select Count(*) From Information_Schema.Columns Where Table_Name = @.t And Column_Name = 'Min_Ind') <> 0)

Begin

-- Rows where the very first column does not have Min_Ind set to Y

Set @.SQL = 'Select * Into ' + @.x + ' From ( Select ' + @.k1 + ' k1,' + @.k2 + ' k2,' + @.r1 + ' r1,' + @.r2 + ' r2, Case When Rank() Over (Partition By ' + @.k1 + ',' + @.k2 + ' Order By ' + @.r1 + ',' + @.r2 + ') = 1 Then ''Y'' Else ''N'' End New_Min_Ind, IsNull(Min_Ind,''-'') Old_Min_Ind From ' + @.t +') t Where Old_Min_Ind <> New_Min_Ind And New_Min_Ind = ''Y'' '

Print @.Sql

Exec(@.SQL)

-- Update any record based on key fields and set the Min_Ind to N if it is not N

Set @.SQL = 'Update a Set Min_Ind = ''N'' From ' + @.x + ' b, ' + @.t + ' a Where b.k1 = a.' + @.k1 + ' And b.k2 = a.' + @.k2 + ' And IsNull(Min_Ind,''-'') <> ''N'' '

Print @.Sql

Exec(@.SQL)

-- Update table and set the Min_Ind to Y based on key and rank fields

Set @.SQL = 'Update a Set Min_Ind = ''Y'' From ' + @.x + ' b, ' + @.t + ' a Where b.k1 = a.' + @.k1 +' And b.k2 = a.' + @.k2 + ' And b.r1 = a.' + @.r1 + ' And b.r2 = a.' + @.r2

Print @.Sql

Exec(@.SQL)

Exec('Drop Table ' + @.x)

End

-- Tables which have a Max_Ind column

IF ((Select Count(*) From Information_Schema.Columns Where Table_Name = @.t And Column_Name = 'Max_Ind') <> 0)

Begin

-- Rows where the very first column does not have Min_Ind set to Y

Set @.SQL = 'Select * Into ' + @.x + ' From ( Select ' + @.k1 + ' k1,' + @.k2 + ' k2,' + @.r1 + ' r1,' + @.r2 +' r2, Case When Rank() Over (Partition By ' + @.k1 + ',' + @.k2 + ' Order By ' + @.r1 + ' Desc,' + @.r2 + ' Desc) = 1 Then ''Y'' Else ''N'' End New_Max_Ind, IsNull(Max_Ind,''-'') Old_Max_Ind From ' + @.t +') t Where Old_Max_Ind <> New_Max_Ind And New_Max_Ind = ''Y'' '

Print @.sql

Exec(@.SQL)

-- Update any record based on key fields and set the Max_Ind to N if it is not N

Set @.SQL = 'Update a Set Max_Ind = ''N'' From ' + @.x + ' b, ' + @.t + ' a Where b.k1 = a.' + @.k1 + ' And b.k2 = a.' + @.k2 + ' And IsNull(Max_Ind,''-'') <> ''N'''

Print @.sql

Exec(@.SQL)

-- Update table and set the Max_Ind to Y based on key and rank fields

Set @.SQL = 'Update a Set Max_Ind = ''Y'' From ' + @.x + ' b, ' + @.t + ' a Where b.k1 = ' + @.k1 +' And b.k2 = ' + @.k2 + ' And b.r1 = ' + @.r1 + ' And b.r2 = ' + @.r2

Print @.sql

Exec(@.SQL)

Exec('Drop Table ' + @.x)

End

Return @.@.Error

End

|||

You will need to prefix your If ... from Information_Schema.Columns with the name of the database passed from your @.t variable...

Something like this

declare @.DBName varchar(50)
declare @.TblName varhcar(50)

Set @.DBName = parsename(@.t, 3)
Set @.TblName = parsename(@.t, 1)

-- 1=object name, 2=schema name, 3=database name, 4= server name

Then you'll need to change your If count(*)... from Information_Schema.Columns statement into dynamic SQL (so that the database name gets interpretted)...

I would use sp_executesql to return your count (from the If statement against information_schema). See BOL on how to return a value using sp_executesql.

Note, just a partial example...

SET @.SQLString = N'Select @.retCountOut = Count(*) From ' + @.dbname + '.Information_Schema.Columns Where Table_Name = ''' + @.tblname + ''' Column_Name = 'Min_Ind''

SET @.ParamDef = N'@.retCountOut int Output, @.dbname varchar(50), @.tblname varchar(50)';

EXECUTE sp_executesql @.SQLString, @.ParamDef, @.dbname = @.dbname, @.tblname = @.tblname, @.retCountout=@.retCount OUTPUT;

If @.retCount<> 0
Begin.....

|||I would strongly suggest against this sort of thing. This code is really hard to follow and will be problematic. I would consider using this code to generate non-dynamic SQL that you run directly. Then the code generator might be dynamic, but each module won't be, and will perform better, and be easier to manage.

Issues on setting the table visibility using an expression

Hi team,

I'm working on Reporting service 2005. When I give an expression for visibility of a table in a report based on a parameter, the contents are coming in a single page and it is not based on the interactive size of the report. I want the data to be coming on different pages based on the interactive size of the page. If the visibility is set directly it works. Could you please help me to solve this issue?

Thanks in advance,

Minu

Hello,

I couldnt resolve this issue. Can you please tell whether its a known issue in reporting service?

Thanks,

Minu

Issue with SQL Parameter when used with % character.

Hi all,

is there a known issue when using a SQL Paramater in a SQL string that uses a '%' character for wildcard string searches?

For example, if I used the following...

SQLCeParameter pKeyword = new SQLCeParameter("@.Keyword", SQLDbType.Char);
pKeyword.value = "Some Text";

string strSQL = "SELECT * FROM tbl_Keywords WHERE (Keyword = @.Keyword%)";

This just never returns and results. But if I run the query in a query analyser window replacing the @.Keyword% with "Some Text", I get results.

I guessing that the placing of the character '%' in the string is the cause maybe...

try putting the % in the string...

pKeyword.value = "Some Text%";

|||

for wildcard searches, you need the LIKE statement. example:

SELECT * FROM someTable WHERE FieldName LIKE @.parameter + '%'

|||Sorry, my query does actually have the LIKE statemet, its just when I post the thread I wrote the query off the top of my head.|||

try this

string strSQL = "SELECT * FROM tbl_Keywords WHERE Keyword LIKE" + @.Keyword + "%";

|||

>>This just never returns and results. <<

What does this mean? It returns nothing? Or it takes forever?

Check the plan of the query using the Display Estimated Plan feature. You should see a very large hotspot in there somewhere that is taking large amounts of the plan (since you are only dealing with one table, it might not be as useful). Then run it without the wildcard and see how the plan looks.

My guess is that it is just taking forever. And is there lots of data? Is that column indexed? An index will work with a LIKE 'value%' criteria, so that shouldn't be a problem. It could be that your indexes aren't optimized too, but first things first :)

|||My Issue is that I no resuts are being returned. I doubt it is taking forever as I am doing a search on a table that has no more that 100 records.

Thanks|||

so have you tried my suggestion earlier?

if you copy and paste your query into query analyzer or SQL management console or something, and execute it - does it bring back results?

so do you get results now, or are you have a perf issue?

another way to increase the perf really is to use stored procedures, if you havent already - safer and faster than standard SQL Injection as you maybe doing ;-)

Issue with populating the parameters

Hi,
I am using crystal report 11.My dynamic parameter displays company codes (4 digits).While populating, company codes are displayed as seperated with commas.But i need to display full 4 digits without commas.
For example:- Database value : 2345 Dynamic parameter displays as 2,345. How to avoid this issue ?Try to use formula having the code totext(field).
or goto format field and dormat it not to have commas

Issue with passing a parameter to Stored Procedure using IN keywor

Hello,
A program I've written creates a parameter to be passed to a stored
procedure based on a user's report selection.
A user can select one, two or three locations.
That parameter is used in an IN clause.
"ZMCC.WERKS IN (@.Locations) "
If a user requests a single location, it works just fine. However, when the
user makes multiple selection, the parameter fails.
I've built the parameter so it looks like " '0031', '0032', '0033' " and a
bunch of variations on this, but none of them work
How can I build the parameter and pass it to the stored procedure?
Thx.
Andy JacobsPlenty of suggestions here:
http://www.sommarskog.se/arrays-in-sql.html
"Andy Jacobs" <AndyJacobs@.discussions.microsoft.com> wrote in message
news:C6146921-878E-49BB-A92B-AFB091FDA821@.microsoft.com...
> Hello,
> A program I've written creates a parameter to be passed to a stored
> procedure based on a user's report selection.
> A user can select one, two or three locations.
> That parameter is used in an IN clause.
> "ZMCC.WERKS IN (@.Locations) "
> If a user requests a single location, it works just fine. However, when
> the
> user makes multiple selection, the parameter fails.
> I've built the parameter so it looks like " '0031', '0032', '0033' " and a
> bunch of variations on this, but none of them work
> How can I build the parameter and pass it to the stored procedure?
> Thx.
> Andy Jacobs
>
>|||http://www.aspfaq.com/2248
"Andy Jacobs" <AndyJacobs@.discussions.microsoft.com> wrote in message
news:C6146921-878E-49BB-A92B-AFB091FDA821@.microsoft.com...
> Hello,
> A program I've written creates a parameter to be passed to a stored
> procedure based on a user's report selection.
> A user can select one, two or three locations.
> That parameter is used in an IN clause.
> "ZMCC.WERKS IN (@.Locations) "
> If a user requests a single location, it works just fine. However, when
> the
> user makes multiple selection, the parameter fails.
> I've built the parameter so it looks like " '0031', '0032', '0033' " and a
> bunch of variations on this, but none of them work
> How can I build the parameter and pass it to the stored procedure?
> Thx.
> Andy Jacobs
>
>|||This one works for me
set @.StrType = ''''+ replace(@.StrType,',',''',''')+''''
Declare @.SQL varchar(5000)
Set @.SQL=
'Select ShipName AS [Cust Name],Reference,ReqNo,CorpName AS [Name],
ReqDate AS [Req Date],RptType AS [Rpt Type],OrderNo AS [Order No],Fee from
#Confirm WHERE RptType IN(' + @.StrType + ')'
--print @.sql
Exec(@.SQL)
END
"Andy Jacobs" <AndyJacobs@.discussions.microsoft.com> wrote in message
news:C6146921-878E-49BB-A92B-AFB091FDA821@.microsoft.com...
> Hello,
> A program I've written creates a parameter to be passed to a stored
> procedure based on a user's report selection.
> A user can select one, two or three locations.
> That parameter is used in an IN clause.
> "ZMCC.WERKS IN (@.Locations) "
> If a user requests a single location, it works just fine. However, when
the
> user makes multiple selection, the parameter fails.
> I've built the parameter so it looks like " '0031', '0032', '0033' " and a
> bunch of variations on this, but none of them work
> How can I build the parameter and pass it to the stored procedure?
> Thx.
> Andy Jacobs
>
>|||SQL is treating the passed variable as a single string, instead of
interpreting the string as a set.
What you will need to do in your stored procedure is parse the string and
insert the values into a temporary table. Then reference that temporary tabl
e
in your select statement with the IN clause.
See http://www.sommarskog.se/arrays-in-sql.html
"Andy Jacobs" wrote:

> Hello,
> A program I've written creates a parameter to be passed to a stored
> procedure based on a user's report selection.
> A user can select one, two or three locations.
> That parameter is used in an IN clause.
> "ZMCC.WERKS IN (@.Locations) "
> If a user requests a single location, it works just fine. However, when th
e
> user makes multiple selection, the parameter fails.
> I've built the parameter so it looks like " '0031', '0032', '0033' " and a
> bunch of variations on this, but none of them work
> How can I build the parameter and pass it to the stored procedure?
> Thx.
> Andy Jacobs
>
>

Issue with incorrect metadata ?

We're running SQL2k5 and I've got some stored procedures which all have the
last parameter as optional with a default value of zero i.e.
create procedure myproc
@.Parm1 int,
@.Parm2 int=0
when I query the system catalogs on this proc the rows returned do not
indicate the parameter as having a default value....I was planning to use
this information but cannot seem to figure out why this is wrong. The
sys.parameters column "has_default_value" is zero for every parameter in all
of our databases....in sys.syscolumns the cdefault is zero as well.
Is there somewhere else to find this data and be able to depend on it? I'm
really stuck here the whole team is waiting on me and I'm supposed to be
providing a home grown solution for automated building of .NET SqlCommand
objects based on this information.
select * from sys.parameters where object_id=2056602615
select * from sys.syscolumns where id=2056602615
> when I query the system catalogs on this proc the rows returned do not
> indicate the parameter as having a default value....I was planning to use
> this information but cannot seem to figure out why this is wrong. The
> sys.parameters column "has_default_value" is zero for every parameter in
> all of our databases....in sys.syscolumns the cdefault is zero as well.
This is true, the information is not stored there (nor in
sys.all_parameters).
I ran a profiler trace and monitored expanding the parameters node under a
stored procedure in Management Studio (which shows "default" / "no default"
but not the actual value). Ignoring names/ids that are specific to my
environment, I saw this (my most relevant observation highlighted on line
13):
SELECT 'Server[@.Name=' + quotename(CAST(serverproperty(N'Servername')
AS sysname),'''') + ']' + '/Database[@.Name=' + quotename(db_name(),'''')
+ ']' + '/StoredProcedure[@.Name=' + quotename(sp.name,'''')
+ ' and @.Schema=' + quotename(SCHEMA_NAME(sp.schema_id),'''')
+ ']' + '/Param[@.Name=' + quotename(param.name,'''') + ']' AS [Urn],
param.name AS [Name],
ISNULL(baset.name, N'') AS [SystemType],
CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar')
AND param.max_length <> -1 THEN param.max_length/2 ELSE
param.max_length END AS int) AS [Length],
CAST(param.precision AS int) AS [NumericPrecision],
CAST(param.scale AS int) AS [NumericScale],
null AS [DefaultValue], -- *********** NOTICE THIS ************
param.is_output AS [IsOutputParameter],
sp.object_id AS [IDText],
db_name() AS [DatabaseName],
param.name AS [ParamName],
CAST(
case
when sp.is_ms_shipped = 1 then 1
when (
select
major_id
from
sys.extended_properties
where
major_id = sp.object_id and
minor_id = 0 and
class = 1 and
name = N'microsoft_database_tools_support')
is not null then 1
else 0
end
AS bit) AS [ParentSysObj],
1 AS [Number]
FROM
sys.all_objects AS sp
INNER JOIN sys.all_parameters AS param
ON param.object_id=sp.object_id
LEFT OUTER JOIN sys.types AS baset
ON baset.user_type_id = param.system_type_id
and baset.user_type_id = baset.system_type_id
WHERE
(sp.type = N'P' OR sp.type = N'RF' OR sp.type='PC')
and(sp.name=N'fakeProcedure'
and SCHEMA_NAME(sp.schema_id)=N'dbo')
ORDER BY
param.parameter_id ASC
Nothing more promising showed up in the trace when scripting the object as
create to new window, or using the modify context menu option. Both seem to
just grab the code from sys.sql_modules and, in the case of modify, change
CREATE to ALTER -- without even bothering with the parameter list at all.
I looked at sp_sproc_columns, which I have spotted in profiler from time to
time, coming from an application that uses ODBC to call stored procedures.
But this procedure does not yield any information about default values. It
gets column_def from spt_sproc_columns_odbc_view (which I can't figure out
how to query directly) but it looks to be always null. I also tried to find
the source for spt_sproc_columns_odbc_view but it seems this may be locked
away in mssqlsystemresource db. The following yielded nothing:
use master;
go
select * from sys.all_objects where name = 'spt_sproc_columns_odbc_view';
select object_definition(object_id('spt_sproc_columns_odb c_view'));
select * from sys.sql_modules where object_id =
object_id('spt_sproc_columns_odbc_view');
select * from sys.system_sql_modules where object_name(object_id) =
'spt_sproc_columns_odbc_view';
Frankly, I think that SQL Server only stores this value in the text in
syscomments / sys.sql_modules. And when the node I mentioned above expands
it must parse the stored procedure text to see whether the parameter
declarations have = signs next to them or not. I couldn't find any other
way to get this information, and I remember it coming up during the beta and
I'm pretty sure it was closed as "won't fix." So unfortunately I think you
are stuck in the same boat; parsing
object_definition(object_id('procedure_name')).
For further information you can see the following article written by me
before SQL Server 2005 was released:
http://databases.aspfaq.com/schema-tutorials/schema-how-do-i-show-the-parameters-for-a-function-or-stored-procedure.html
And this BOL article for SQL Server 2005,
http://msdn2.microsoft.com/en-us/library/ms190340.aspx
Which says:
"SQL Server only maintains default values for CLR objects in this catalog
view; therefore, this column has a value of 0 for Transact-SQL objects. To
view the default value of a parameter in a Transact-SQL object, query the
definition column of the sys.sql_modules catalog view, or use the
OBJECT_DEFINITION system function."
I have submitted a request for more clarification, and will follow up if I
get any useful information.
Cheers,
Aaron
|||> sys.parameters column "has_default_value" is zero for every parameter in
> all of our databases....in sys.syscolumns the cdefault is zero as well.
I have submitted a suggestion to Microsoft regarding this issue through
"official" channels.
If you have a passport / Windows Live ID, you can see my feedback here, and
vote if you feel strongly enough about it:
http://connect.microsoft.com/feedback/viewfeedback.aspx?FeedbackID=234143
|||Thanks for that reference...gives me alot to go on...
I wasn't trying to get the default value for a parameter...just the
knowledge that a parameter has a default value and can be considered
optional for input....
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Op7OOSY$GHA.1220@.TK2MSFTNGP04.phx.gbl...
> Books Online is pretty clear on this. Here's a quote from sys.parameters,
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/24e2764b-c8e5-4322-97a4-7407d8b8a92b.htm
> :
> "SQL Server only maintains default values for CLR objects in this catalog
> view; therefore, this column has a value of 0 for Transact-SQL objects. To
> view the default value of a parameter in a Transact-SQL object, query the
> definition column of the sys.sql_modules catalog view, or use the
> OBJECT_DEFINITION system function."
> It has always been the case that we cannot get the default values of
> parameters in SQL Server. Seems we now can get it for CLR procedures, but
> still not for TSQL objects. So same applies as for earlier versions: parse
> the source code. You might want to post an enhancement request at:
> http://connect.microsoft.com/site/sitehome.aspx?SiteID=68
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:epO1UuT$GHA.4704@.TK2MSFTNGP04.phx.gbl...
>
|||Voted!!!
Thanks
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23AyWj3X$GHA.2328@.TK2MSFTNGP02.phx.gbl...
> I have submitted a suggestion to Microsoft regarding this issue through
> "official" channels.
> If you have a passport / Windows Live ID, you can see my feedback here,
> and vote if you feel strongly enough about it:
> http://connect.microsoft.com/feedback/viewfeedback.aspx?FeedbackID=234143
>
|||Here is a workaround for the time being (also posting it to the issue on
Connect).
I am also working on a version that retrieves the explicit default value,
but that is proving more cumbersome if the default value is a string and
contains a comma (but I am close).
ALTER PROCEDURE dbo.sys_GetParameters
@.object_name NVARCHAR(511)
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@.object_id INT,
@.paramID INT,
@.paramName SYSNAME,
@.definition NVARCHAR(MAX),
@.t NVARCHAR(MAX),
@.loc1 INT,
@.loc2 INT,
@.loc3 INT,
@.loc4 INT,
@.has_default_value BIT;
SET @.object_id = OBJECT_ID(@.object_name);
IF (@.object_id IS NOT NULL)
BEGIN
SELECT @.definition = OBJECT_DEFINITION(@.object_id);
CREATE TABLE #params
(
parameter_id INT PRIMARY KEY,
has_default_value BIT NOT NULL DEFAULT (0)
);
DECLARE c CURSOR
LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT
parameter_id,
[name]
FROM
sys.parameters
WHERE
[object_id] = @.object_id;
OPEN c;
FETCH NEXT FROM c INTO @.paramID, @.paramName;
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
SELECT
@.t = SUBSTRING
(
@.definition,
CHARINDEX(@.paramName, @.definition),
4000
),
@.has_default_value = 0;
SET @.loc1 = COALESCE(NULLIF(CHARINDEX('''', @.t), 0), 4000);
SET @.loc2 = COALESCE(NULLIF(CHARINDEX(',', @.t), 0), 4000);
SET @.loc3 = NULLIF(CHARINDEX('OUTPUT', @.t), 0);
SET @.loc4 = NULLIF(CHARINDEX('AS', @.t), 0);
SET @.loc1 = CASE WHEN @.loc2 < @.loc1 THEN @.loc2 ELSE @.loc1 END;
SET @.loc1 = CASE WHEN @.loc3 < @.loc1 THEN @.loc3 ELSE @.loc1 END;
SET @.loc1 = CASE WHEN @.loc4 < @.loc1 THEN @.loc4 ELSE @.loc1 END;
IF CHARINDEX('=', LTRIM(RTRIM(SUBSTRING(@.t, 1, @.loc1)))) > 0
SET @.has_default_value = 1;
INSERT #params
(
parameter_id,
has_default_value
)
SELECT
@.paramID,
@.has_default_value;
FETCH NEXT FROM c INTO @.paramID, @.paramName;
END
SELECT
sp.[object_id],
[object_name] = @.object_name,
param_name = sp.[name],
sp.parameter_id,
type_name = UPPER(st.[name]),
sp.max_length,
sp.[precision],
sp.scale,
sp.is_output,
p.has_default_value
FROM
sys.parameters sp
INNER JOIN
#params p
ON
sp.parameter_id = p.parameter_id
INNER JOIN
sys.types st
ON
sp.user_type_id = st.user_type_id
WHERE
sp.[object_id] = @.object_id;
CLOSE c;
DEALLOCATE c;
DROP TABLE #params;
END
END
GO

Issue with incorrect metadata ?

We're running SQL2k5 and I've got some stored procedures which all have the
last parameter as optional with a default value of zero i.e.
create procedure myproc
@.Parm1 int,
@.Parm2 int=0
when I query the system catalogs on this proc the rows returned do not
indicate the parameter as having a default value....I was planning to use
this information but cannot seem to figure out why this is wrong. The
sys.parameters column "has_default_value" is zero for every parameter in all
of our databases....in sys.syscolumns the cdefault is zero as well.
Is there somewhere else to find this data and be able to depend on it? I'm
really stuck here the whole team is waiting on me and I'm supposed to be
providing a home grown solution for automated building of .NET SqlCommand
objects based on this information.
select * from sys.parameters where object_id=2056602615
select * from sys.syscolumns where id=2056602615> when I query the system catalogs on this proc the rows returned do not
> indicate the parameter as having a default value....I was planning to use
> this information but cannot seem to figure out why this is wrong. The
> sys.parameters column "has_default_value" is zero for every parameter in
> all of our databases....in sys.syscolumns the cdefault is zero as well.
This is true, the information is not stored there (nor in
sys.all_parameters).
I ran a profiler trace and monitored expanding the parameters node under a
stored procedure in Management Studio (which shows "default" / "no default"
but not the actual value). Ignoring names/ids that are specific to my
environment, I saw this (my most relevant observation highlighted on line
13):
SELECT 'Server[@.Name=' + quotename(CAST(serverproperty(N'Servername')
AS sysname),'''') + ']' + '/Database[@.Name=' + quotename(db_name(),'''')
+ ']' + '/StoredProcedure[@.Name=' + quotename(sp.name,'''')
+ ' and @.Schema=' + quotename(SCHEMA_NAME(sp.schema_id),'''')
+ ']' + '/Param[@.Name=' + quotename(param.name,'''') + ']' AS [Urn],
param.name AS [Name],
ISNULL(baset.name, N'') AS [SystemType],
CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar')
AND param.max_length <> -1 THEN param.max_length/2 ELSE
param.max_length END AS int) AS [Length],
CAST(param.precision AS int) AS [NumericPrecision],
CAST(param.scale AS int) AS [NumericScale],
null AS [DefaultValue], -- *********** NOTICE THIS ************
param.is_output AS [IsOutputParameter],
sp.object_id AS [IDText],
db_name() AS [DatabaseName],
param.name AS [ParamName],
CAST(
case
when sp.is_ms_shipped = 1 then 1
when (
select
major_id
from
sys.extended_properties
where
major_id = sp.object_id and
minor_id = 0 and
class = 1 and
name = N'microsoft_database_tools_support')
is not null then 1
else 0
end
AS bit) AS [ParentSysObj],
1 AS [Number]
FROM
sys.all_objects AS sp
INNER JOIN sys.all_parameters AS param
ON param.object_id=sp.object_id
LEFT OUTER JOIN sys.types AS baset
ON baset.user_type_id = param.system_type_id
and baset.user_type_id = baset.system_type_id
WHERE
(sp.type = N'P' OR sp.type = N'RF' OR sp.type='PC')
and(sp.name=N'fakeProcedure'
and SCHEMA_NAME(sp.schema_id)=N'dbo')
ORDER BY
param.parameter_id ASC
Nothing more promising showed up in the trace when scripting the object as
create to new window, or using the modify context menu option. Both seem to
just grab the code from sys.sql_modules and, in the case of modify, change
CREATE to ALTER -- without even bothering with the parameter list at all.
I looked at sp_sproc_columns, which I have spotted in profiler from time to
time, coming from an application that uses ODBC to call stored procedures.
But this procedure does not yield any information about default values. It
gets column_def from spt_sproc_columns_odbc_view (which I can't figure out
how to query directly) but it looks to be always null. I also tried to find
the source for spt_sproc_columns_odbc_view but it seems this may be locked
away in mssqlsystemresource db. The following yielded nothing:
use master;
go
select * from sys.all_objects where name = 'spt_sproc_columns_odbc_view';
select object_definition(object_id('spt_sproc_columns_odbc_view'));
select * from sys.sql_modules where object_id =object_id('spt_sproc_columns_odbc_view');
select * from sys.system_sql_modules where object_name(object_id) ='spt_sproc_columns_odbc_view';
Frankly, I think that SQL Server only stores this value in the text in
syscomments / sys.sql_modules. And when the node I mentioned above expands
it must parse the stored procedure text to see whether the parameter
declarations have = signs next to them or not. I couldn't find any other
way to get this information, and I remember it coming up during the beta and
I'm pretty sure it was closed as "won't fix." So unfortunately I think you
are stuck in the same boat; parsing
object_definition(object_id('procedure_name')).
For further information you can see the following article written by me
before SQL Server 2005 was released:
http://databases.aspfaq.com/schema-tutorials/schema-how-do-i-show-the-parameters-for-a-function-or-stored-procedure.html
And this BOL article for SQL Server 2005,
http://msdn2.microsoft.com/en-us/library/ms190340.aspx
Which says:
"SQL Server only maintains default values for CLR objects in this catalog
view; therefore, this column has a value of 0 for Transact-SQL objects. To
view the default value of a parameter in a Transact-SQL object, query the
definition column of the sys.sql_modules catalog view, or use the
OBJECT_DEFINITION system function."
I have submitted a request for more clarification, and will follow up if I
get any useful information.
Cheers,
Aaron|||> sys.parameters column "has_default_value" is zero for every parameter in
> all of our databases....in sys.syscolumns the cdefault is zero as well.
I have submitted a suggestion to Microsoft regarding this issue through
"official" channels.
If you have a passport / Windows Live ID, you can see my feedback here, and
vote if you feel strongly enough about it:
http://connect.microsoft.com/feedback/viewfeedback.aspx?FeedbackID=234143|||Books Online is pretty clear on this. Here's a quote from sys.parameters,
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/24e2764b-c8e5-4322-97a4-7407d8b8a92b.htm :
"SQL Server only maintains default values for CLR objects in this catalog view; therefore, this
column has a value of 0 for Transact-SQL objects. To view the default value of a parameter in a
Transact-SQL object, query the definition column of the sys.sql_modules catalog view, or use the
OBJECT_DEFINITION system function."
It has always been the case that we cannot get the default values of parameters in SQL Server. Seems
we now can get it for CLR procedures, but still not for TSQL objects. So same applies as for earlier
versions: parse the source code. You might want to post an enhancement request at:
http://connect.microsoft.com/site/sitehome.aspx?SiteID=68
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:epO1UuT$GHA.4704@.TK2MSFTNGP04.phx.gbl...
> We're running SQL2k5 and I've got some stored procedures which all have the last parameter as
> optional with a default value of zero i.e.
> create procedure myproc
> @.Parm1 int,
> @.Parm2 int=0
> when I query the system catalogs on this proc the rows returned do not indicate the parameter as
> having a default value....I was planning to use this information but cannot seem to figure out
> why this is wrong. The sys.parameters column "has_default_value" is zero for every parameter in
> all of our databases....in sys.syscolumns the cdefault is zero as well.
> Is there somewhere else to find this data and be able to depend on it? I'm really stuck here the
> whole team is waiting on me and I'm supposed to be providing a home grown solution for automated
> building of .NET SqlCommand objects based on this information.
> select * from sys.parameters where object_id=2056602615
> select * from sys.syscolumns where id=2056602615
>|||Thanks for that reference...gives me alot to go on...
I wasn't trying to get the default value for a parameter...just the
knowledge that a parameter has a default value and can be considered
optional for input....
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Op7OOSY$GHA.1220@.TK2MSFTNGP04.phx.gbl...
> Books Online is pretty clear on this. Here's a quote from sys.parameters,
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/24e2764b-c8e5-4322-97a4-7407d8b8a92b.htm
> :
> "SQL Server only maintains default values for CLR objects in this catalog
> view; therefore, this column has a value of 0 for Transact-SQL objects. To
> view the default value of a parameter in a Transact-SQL object, query the
> definition column of the sys.sql_modules catalog view, or use the
> OBJECT_DEFINITION system function."
> It has always been the case that we cannot get the default values of
> parameters in SQL Server. Seems we now can get it for CLR procedures, but
> still not for TSQL objects. So same applies as for earlier versions: parse
> the source code. You might want to post an enhancement request at:
> http://connect.microsoft.com/site/sitehome.aspx?SiteID=68
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:epO1UuT$GHA.4704@.TK2MSFTNGP04.phx.gbl...
>> We're running SQL2k5 and I've got some stored procedures which all have
>> the last parameter as optional with a default value of zero i.e.
>> create procedure myproc
>> @.Parm1 int,
>> @.Parm2 int=0
>> when I query the system catalogs on this proc the rows returned do not
>> indicate the parameter as having a default value....I was planning to
>> use this information but cannot seem to figure out why this is wrong.
>> The sys.parameters column "has_default_value" is zero for every parameter
>> in all of our databases....in sys.syscolumns the cdefault is zero as
>> well.
>> Is there somewhere else to find this data and be able to depend on it?
>> I'm really stuck here the whole team is waiting on me and I'm supposed to
>> be providing a home grown solution for automated building of .NET
>> SqlCommand objects based on this information.
>> select * from sys.parameters where object_id=2056602615
>> select * from sys.syscolumns where id=2056602615
>>
>|||Voted!!!
Thanks
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23AyWj3X$GHA.2328@.TK2MSFTNGP02.phx.gbl...
>> sys.parameters column "has_default_value" is zero for every parameter in
>> all of our databases....in sys.syscolumns the cdefault is zero as well.
> I have submitted a suggestion to Microsoft regarding this issue through
> "official" channels.
> If you have a passport / Windows Live ID, you can see my feedback here,
> and vote if you feel strongly enough about it:
> http://connect.microsoft.com/feedback/viewfeedback.aspx?FeedbackID=234143
>|||Here is a workaround for the time being (also posting it to the issue on
Connect).
I am also working on a version that retrieves the explicit default value,
but that is proving more cumbersome if the default value is a string and
contains a comma (but I am close).
ALTER PROCEDURE dbo.sys_GetParameters
@.object_name NVARCHAR(511)
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@.object_id INT,
@.paramID INT,
@.paramName SYSNAME,
@.definition NVARCHAR(MAX),
@.t NVARCHAR(MAX),
@.loc1 INT,
@.loc2 INT,
@.loc3 INT,
@.loc4 INT,
@.has_default_value BIT;
SET @.object_id = OBJECT_ID(@.object_name);
IF (@.object_id IS NOT NULL)
BEGIN
SELECT @.definition = OBJECT_DEFINITION(@.object_id);
CREATE TABLE #params
(
parameter_id INT PRIMARY KEY,
has_default_value BIT NOT NULL DEFAULT (0)
);
DECLARE c CURSOR
LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT
parameter_id,
[name]
FROM
sys.parameters
WHERE
[object_id] = @.object_id;
OPEN c;
FETCH NEXT FROM c INTO @.paramID, @.paramName;
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
SELECT
@.t = SUBSTRING
(
@.definition,
CHARINDEX(@.paramName, @.definition),
4000
),
@.has_default_value = 0;
SET @.loc1 = COALESCE(NULLIF(CHARINDEX('''', @.t), 0), 4000);
SET @.loc2 = COALESCE(NULLIF(CHARINDEX(',', @.t), 0), 4000);
SET @.loc3 = NULLIF(CHARINDEX('OUTPUT', @.t), 0);
SET @.loc4 = NULLIF(CHARINDEX('AS', @.t), 0);
SET @.loc1 = CASE WHEN @.loc2 < @.loc1 THEN @.loc2 ELSE @.loc1 END;
SET @.loc1 = CASE WHEN @.loc3 < @.loc1 THEN @.loc3 ELSE @.loc1 END;
SET @.loc1 = CASE WHEN @.loc4 < @.loc1 THEN @.loc4 ELSE @.loc1 END;
IF CHARINDEX('=', LTRIM(RTRIM(SUBSTRING(@.t, 1, @.loc1)))) > 0
SET @.has_default_value = 1;
INSERT #params
(
parameter_id,
has_default_value
)
SELECT
@.paramID,
@.has_default_value;
FETCH NEXT FROM c INTO @.paramID, @.paramName;
END
SELECT
sp.[object_id],
[object_name] = @.object_name,
param_name = sp.[name],
sp.parameter_id,
type_name = UPPER(st.[name]),
sp.max_length,
sp.[precision],
sp.scale,
sp.is_output,
p.has_default_value
FROM
sys.parameters sp
INNER JOIN
#params p
ON
sp.parameter_id = p.parameter_id
INNER JOIN
sys.types st
ON
sp.user_type_id = st.user_type_id
WHERE
sp.[object_id] = @.object_id;
CLOSE c;
DEALLOCATE c;
DROP TABLE #params;
END
END
GO

Issue with incorrect metadata ?

We're running SQL2k5 and I've got some stored procedures which all have the
last parameter as optional with a default value of zero i.e.
create procedure myproc
@.Parm1 int,
@.Parm2 int=0
when I query the system catalogs on this proc the rows returned do not
indicate the parameter as having a default value....I was planning to use
this information but cannot seem to figure out why this is wrong. The
sys.parameters column "has_default_value" is zero for every parameter in all
of our databases....in sys.syscolumns the cdefault is zero as well.
Is there somewhere else to find this data and be able to depend on it? I'm
really stuck here the whole team is waiting on me and I'm supposed to be
providing a home grown solution for automated building of .NET SqlCommand
objects based on this information.
select * from sys.parameters where object_id=2056602615
select * from sys.syscolumns where id=2056602615> when I query the system catalogs on this proc the rows returned do not
> indicate the parameter as having a default value....I was planning to use
> this information but cannot seem to figure out why this is wrong. The
> sys.parameters column "has_default_value" is zero for every parameter in
> all of our databases....in sys.syscolumns the cdefault is zero as well.
This is true, the information is not stored there (nor in
sys.all_parameters).
I ran a profiler trace and monitored expanding the parameters node under a
stored procedure in Management Studio (which shows "default" / "no default"
but not the actual value). Ignoring names/ids that are specific to my
environment, I saw this (my most relevant observation highlighted on line
13):
SELECT 'Server[@.Name=' + quotename(CAST(serverproperty(N'Serverna
me')
AS sysname),'''') + ']' + '/Database[@.Name=' + quotename(db_name(),'''')
+ ']' + '/StoredProcedure[@.Name=' + quotename(sp.name,'''')
+ ' and @.Schema=' + quotename(SCHEMA_NAME(sp.schema_id),'''')
+ ']' + '/Param[@.Name=' + quotename(param.name,'''') + ']' AS [Urn],
param.name AS [Name],
ISNULL(baset.name, N'') AS [SystemType],
CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar')
AND param.max_length <> -1 THEN param.max_length/2 ELSE
param.max_length END AS int) AS [Length],
CAST(param.precision AS int) AS [NumericPrecision],
CAST(param.scale AS int) AS [NumericScale],
null AS [DefaultValue], -- *********** NOTICE THIS ************
param.is_output AS [IsOutputParameter],
sp.object_id AS [IDText],
db_name() AS [DatabaseName],
param.name AS [ParamName],
CAST(
case
when sp.is_ms_shipped = 1 then 1
when (
select
major_id
from
sys.extended_properties
where
major_id = sp.object_id and
minor_id = 0 and
class = 1 and
name = N'microsoft_database_tools_support')
is not null then 1
else 0
end
AS bit) AS [ParentSysObj],
1 AS [Number]
FROM
sys.all_objects AS sp
INNER JOIN sys.all_parameters AS param
ON param.object_id=sp.object_id
LEFT OUTER JOIN sys.types AS baset
ON baset.user_type_id = param.system_type_id
and baset.user_type_id = baset.system_type_id
WHERE
(sp.type = N'P' OR sp.type = N'RF' OR sp.type='PC')
and(sp.name=N'fakeProcedure'
and SCHEMA_NAME(sp.schema_id)=N'dbo')
ORDER BY
param.parameter_id ASC
Nothing more promising showed up in the trace when scripting the object as
create to new window, or using the modify context menu option. Both seem to
just grab the code from sys.sql_modules and, in the case of modify, change
CREATE to ALTER -- without even bothering with the parameter list at all.
I looked at sp_sproc_columns, which I have spotted in profiler from time to
time, coming from an application that uses ODBC to call stored procedures.
But this procedure does not yield any information about default values. It
gets column_def from spt_sproc_columns_odbc_view (which I can't figure out
how to query directly) but it looks to be always null. I also tried to find
the source for spt_sproc_columns_odbc_view but it seems this may be locked
away in mssqlsystemresource db. The following yielded nothing:
use master;
go
select * from sys.all_objects where name = 'spt_sproc_columns_odbc_view';
select object_definition(object_id('spt_sproc_c
olumns_odbc_view'));
select * from sys.sql_modules where object_id =
object_id('spt_sproc_columns_odbc_view')
;
select * from sys.system_sql_modules where object_name(object_id) =
'spt_sproc_columns_odbc_view';
Frankly, I think that SQL Server only stores this value in the text in
syscomments / sys.sql_modules. And when the node I mentioned above expands
it must parse the stored procedure text to see whether the parameter
declarations have = signs next to them or not. I couldn't find any other
way to get this information, and I remember it coming up during the beta and
I'm pretty sure it was closed as "won't fix." So unfortunately I think you
are stuck in the same boat; parsing
object_definition(object_id('procedure_n
ame')).
For further information you can see the following article written by me
before SQL Server 2005 was released:
http://databases.aspfaq.com/schema-...-procedure.html
And this BOL article for SQL Server 2005,
http://msdn2.microsoft.com/en-us/library/ms190340.aspx
Which says:
"SQL Server only maintains default values for CLR objects in this catalog
view; therefore, this column has a value of 0 for Transact-SQL objects. To
view the default value of a parameter in a Transact-SQL object, query the
definition column of the sys.sql_modules catalog view, or use the
OBJECT_DEFINITION system function."
I have submitted a request for more clarification, and will follow up if I
get any useful information.
Cheers,
Aaron|||> sys.parameters column "has_default_value" is zero for every parameter in
> all of our databases....in sys.syscolumns the cdefault is zero as well.
I have submitted a suggestion to Microsoft regarding this issue through
"official" channels.
If you have a passport / Windows Live ID, you can see my feedback here, and
vote if you feel strongly enough about it:
http://connect.microsoft.com/feedba...edbackID=234143|||Books Online is pretty clear on this. Here's a quote from sys.parameters,
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/24e2764b-c8e5-4322-97a4-
7407d8b8a92b.htm :
"SQL Server only maintains default values for CLR objects in this catalog vi
ew; therefore, this
column has a value of 0 for Transact-SQL objects. To view the default value
of a parameter in a
Transact-SQL object, query the definition column of the sys.sql_modules cata
log view, or use the
OBJECT_DEFINITION system function."
It has always been the case that we cannot get the default values of paramet
ers in SQL Server. Seems
we now can get it for CLR procedures, but still not for TSQL objects. So sam
e applies as for earlier
versions: parse the source code. You might want to post an enhancement reque
st at:
http://connect.microsoft.com/site/s...aspx?SiteID=68
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:epO1UuT$GHA.4704@.TK2MSFTNGP04.phx.gbl...
> We're running SQL2k5 and I've got some stored procedures which all have th
e last parameter as
> optional with a default value of zero i.e.
> create procedure myproc
> @.Parm1 int,
> @.Parm2 int=0
> when I query the system catalogs on this proc the rows returned do not ind
icate the parameter as
> having a default value....I was planning to use this information but cann
ot seem to figure out
> why this is wrong. The sys.parameters column "has_default_value" is zero
for every parameter in
> all of our databases....in sys.syscolumns the cdefault is zero as well.
> Is there somewhere else to find this data and be able to depend on it? I'
m really stuck here the
> whole team is waiting on me and I'm supposed to be providing a home grown
solution for automated
> building of .NET SqlCommand objects based on this information.
> select * from sys.parameters where object_id=2056602615
> select * from sys.syscolumns where id=2056602615
>|||Thanks for that reference...gives me alot to go on...
I wasn't trying to get the default value for a parameter...just the
knowledge that a parameter has a default value and can be considered
optional for input....
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Op7OOSY$GHA.1220@.TK2MSFTNGP04.phx.gbl...
> Books Online is pretty clear on this. Here's a quote from sys.parameters,
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/24e2764b-c8e5-4322-97a
4-7407d8b8a92b.htm
> :
> "SQL Server only maintains default values for CLR objects in this catalog
> view; therefore, this column has a value of 0 for Transact-SQL objects. To
> view the default value of a parameter in a Transact-SQL object, query the
> definition column of the sys.sql_modules catalog view, or use the
> OBJECT_DEFINITION system function."
> It has always been the case that we cannot get the default values of
> parameters in SQL Server. Seems we now can get it for CLR procedures, but
> still not for TSQL objects. So same applies as for earlier versions: parse
> the source code. You might want to post an enhancement request at:
> http://connect.microsoft.com/site/s...aspx?SiteID=68
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:epO1UuT$GHA.4704@.TK2MSFTNGP04.phx.gbl...
>|||Voted!!!
Thanks
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:%23AyWj3X$GHA.2328@.TK2MSFTNGP02.phx.gbl...
> I have submitted a suggestion to Microsoft regarding this issue through
> "official" channels.
> If you have a passport / Windows Live ID, you can see my feedback here,
> and vote if you feel strongly enough about it:
> http://connect.microsoft.com/feedba...edbackID=234143
>|||Here is a workaround for the time being (also posting it to the issue on
Connect).
I am also working on a version that retrieves the explicit default value,
but that is proving more cumbersome if the default value is a string and
contains a comma (but I am close).
ALTER PROCEDURE dbo.sys_GetParameters
@.object_name NVARCHAR(511)
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@.object_id INT,
@.paramID INT,
@.paramName SYSNAME,
@.definition NVARCHAR(MAX),
@.t NVARCHAR(MAX),
@.loc1 INT,
@.loc2 INT,
@.loc3 INT,
@.loc4 INT,
@.has_default_value BIT;
SET @.object_id = OBJECT_ID(@.object_name);
IF (@.object_id IS NOT NULL)
BEGIN
SELECT @.definition = OBJECT_DEFINITION(@.object_id);
CREATE TABLE #params
(
parameter_id INT PRIMARY KEY,
has_default_value BIT NOT NULL DEFAULT (0)
);
DECLARE c CURSOR
LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT
parameter_id,
[name]
FROM
sys.parameters
WHERE
[object_id] = @.object_id;
OPEN c;
FETCH NEXT FROM c INTO @.paramID, @.paramName;
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
SELECT
@.t = SUBSTRING
(
@.definition,
CHARINDEX(@.paramName, @.definition),
4000
),
@.has_default_value = 0;
SET @.loc1 = COALESCE(NULLIF(CHARINDEX('''', @.t), 0), 4000);
SET @.loc2 = COALESCE(NULLIF(CHARINDEX(',', @.t), 0), 4000);
SET @.loc3 = NULLIF(CHARINDEX('OUTPUT', @.t), 0);
SET @.loc4 = NULLIF(CHARINDEX('AS', @.t), 0);
SET @.loc1 = CASE WHEN @.loc2 < @.loc1 THEN @.loc2 ELSE @.loc1 END;
SET @.loc1 = CASE WHEN @.loc3 < @.loc1 THEN @.loc3 ELSE @.loc1 END;
SET @.loc1 = CASE WHEN @.loc4 < @.loc1 THEN @.loc4 ELSE @.loc1 END;
IF CHARINDEX('=', LTRIM(RTRIM(SUBSTRING(@.t, 1, @.loc1)))) > 0
SET @.has_default_value = 1;
INSERT #params
(
parameter_id,
has_default_value
)
SELECT
@.paramID,
@.has_default_value;
FETCH NEXT FROM c INTO @.paramID, @.paramName;
END
SELECT
sp.[object_id],
[object_name] = @.object_name,
param_name = sp.[name],
sp.parameter_id,
type_name = UPPER(st.[name]),
sp.max_length,
sp.[precision],
sp.scale,
sp.is_output,
p.has_default_value
FROM
sys.parameters sp
INNER JOIN
#params p
ON
sp.parameter_id = p.parameter_id
INNER JOIN
sys.types st
ON
sp.user_type_id = st.user_type_id
WHERE
sp.[object_id] = @.object_id;
CLOSE c;
DEALLOCATE c;
DROP TABLE #params;
END
END
GO

Issue incorporating third parameter in report

I'm having issues incorporating a third parameter into my existing 2 parameters. I currently have them all defined and referenced in all queries, but the problem is with the criteria I think. I can get 2 of them to work together either 1&2 or 2&3 (by removing the other one), but not 1,2&3 at the same time. I'm using or conditions. Is there something I'm missing? thanks.

Like I said all my datasets referrence all of these parameters 1,2&3. On another note, when I'm in the preview area and I choose a value from the third parameter and the 2nd and click view, the screen just blinks (nothing will retrieve unless I choose a parameter for 1).

Also, when I deploy it on the web server and I select values for 2&3 and click view, it says to select a value for 1. I went back into my criteria for each dataset and check the parameters and they all look correct. Here is a sample of the condition I'm using for each dataset I'm retrieving:

where (X=1 and Y=2) or (X=3 and Y=2). Logically thinking, this is what I think it should be.

Thanks in advance for the help!

Hi,

Set the parameters to allow null values and change your query:

="select some_columns from your_table where some_conditions and " & iif(Parameters!Var1.Value is Nothing,"( col3=" & Parameters!Var3.Value & " and col2=" & Parameters!Var2.Value & " )" ,"( col1=" & Parameters!Var1.Value & " and col2=" & Parameters!Var2.Value & " )" )

To preview if this works as expected just copy the statement to a textfield and verify it looks like you want it to look
|||

I keep getting unclosed quotation mark near the character string ')' somewhere on the second line.

To be more specific, Here is the exact way may query looks:

SELECT CONVERT(text, CASE WHEN fra.percent_planned_complete LIKE '[0-9].[0-9][0-9]~%' ESCAPE '~' OR
fra.percent_planned_complete LIKE '[0-9][0-9].[0-9][0-9]~%' ESCAPE '~' OR
fra.percent_planned_complete = '100.00%' THEN cast(cast(ROUND(CAST(stuff(fra.percent_planned_complete, len(fra.percent_planned_complete), 1, '')
AS DECIMAL(5, 2)), 0) AS int) AS varchar(30)) + '%' ELSE fra.percent_planned_complete END) AS FORMATTED_Planned_PCT_COMPLETE,
CONVERT(text, Percent_Complete_To_Date + '%') AS FORMATTED_PCT_COMPLETE, PROJECTMANAGER, REPORTDATE, CAPITALPROJECTNUMBER,
PROJECTNAME
FROM FRA
WHERE (PROJECTNAME = @.proj_name) AND (REPORTDATE = @.report_month) OR
(CAPITALPROJECTNUMBER = @.cap_num) AND (REPORTDATE = @.report_month) and " & iif (Parameters!proj_name.Value is null,"( REPORTDATE=" & Parameters!@.report_month.Value & " and CAPITALPROJECTNUMBER=" & Parameters!@.cap_num.Value & " )" ,"( PROJECTNAME=" & Parameters!@.proj_name.Value & " and REPORTDATE=" & Parameters!@.report_month.Value & " )" ).

Also when I run just:

"SELECT REPORTDATE, CAPITALPROJECTNUMBER,
PROJECTNAME
FROM FRA
WHERE (PROJECTNAME = @.proj_name) AND (REPORTDATE = @.report_month) OR
(CAPITALPROJECTNUMBER = @.cap_num) AND (REPORTDATE = @.report_month) and " & iif (Parameters!proj_name.Value is null,"( REPORTDATE=" & Parameters!@.report_month.Value & " and CAPITALPROJECTNUMBER=" & Parameters!@.cap_num.Value & " )" ,"( PROJECTNAME=" & Parameters!@.proj_name.Value & " and REPORTDATE=" & Parameters!@.report_month.Value & " )" ).

I get maximum length is 128 and incorrect syntax by the Select

I keep trying to resolve this, but can't seem to do it.