Is this SQL stored prodcedure is valid

what i want to achive is the proc sh'd return a master-detail value in one go.
master value should be returned with Out Parameter and detail value as a recordset.

will it return the recordset of detail table as below...

Create Procedure ProductDetail
(
 @.ProductID int,
 @.ProductCode varchar(15) OUTPUT,
 @.ProductName varchar(60) OUTPUT,
 @.CategoryID int OUTPUT,
 @.CategoryName varchar(60) OUTPUT,
 @.Image1 varchar(256) OUTPUT,
 @.Image2 varchar(256) OUTPUT,
 @.UnitPrice smallmoney OUTPUT,
 @.UOMValue numeric(9) OUTPUT,
 @.UOMName varchar(10) OUTPUT,
 @.ShippingWeight numeric(9) OUTPUT,
 @.Directions varchar(1500) OUTPUT,
 @.Ingrediants varchar(1500) OUTPUT,
 @.Warnings varchar(1500) OUTPUT,
 @.ShortDescription varchar(1000) OUTPUT,
 @.LongDescription varchar(2000) OUTPUT,
 @.NutritionFacts varchar(1000) OUTPUT,
 @.SearchKeywords varchar(500) OUTPUT,
 @.IsTaxable varchar(15) OUTPUT,
 @.CreatedBy varchar(60) OUTPUT,
 @.CreatedOn varchar(15) OUTPUT,
 @.UpdatedBy varchar(60) OUTPUT,
 @.UpdatedOn varchar(15) OUTPUT,
 @.Status int OUTPUT
)
AS
SELECT
 @.ProductCode = ProductCode,
 @.ProductName = ProductName,
 @.CategoryID = CategoryID,
 @.CategoryName = (select CategoryName from mCategory where CategoryID=a.CategoryID),
 @.Image1 = isnull(Image1,''),
 @.Image2 = isnull(Image1,''),
 @.UnitPrice = isnull(UnitPrice,0),
 @.UOMValue = isnull(UOMValue,0),
 @.UOMName = isnull(UOMName,''),
 @.ShippingWeight = isnull(ShippingWeight,0),
 @.Directions = isnull(Directions,''),
 @.Ingrediants = isnull(Ingrediants,''),
 @.Warnings = isnull(Warnings,''),
 @.ShortDescription = isnull(ShortDesc,''),
 @.LongDescription = isnull(LongDesc,''),
 @.NutritionFacts = isnull(NutritionFacts,''),
 @.SearchKeywords = isnull(SearchKeywords,''),
 @.IsTaxable = case when isnull(IsTaxable,0)=0 then 'No' else 'Yes' End,
 @.CreatedBy = isnull((select LName + ',' + FName from mUser where UserID=InsertedBy),''),
 @.CreatedOn = InsertedOn,
 @.UpdatedBy = isnull((select LName + ',' + FName from mUser where UserID=UpdatedBy),''),
 @.UpdatedOn = UpdatedOn,
 @.Status = Convert(int,isnull(Status,0))
FROM
 mProduct a
WHERE
 ProductID = @.ProductID
SELECT
 ID as PricingDetailID,
 isnull(PricingFromQnty,0) as PricingFromQnty,
 isnull(PricingToQnty,0) as PricingToQnty,
 isnull(RangePrice,0) as RangePrice,
 Convert(int,isnull(Status,0))as Status
FROM
 dProduct
WHERE
 ProductID = @.CategoryID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Regards,
BhairavI believe the way you are doing it is possible, but why not return two recordsets back to a dataset? Then you would have a master datatable and detail datatable. I believe this would work:

Create Procedure ProductDetail
(
 @.ProductID int
)
AS
SELECT
 ProductCode,
 ProductName,
 CategoryID,
 (select CategoryName from mCategory where CategoryID=a.CategoryID),
 isnull(Image1,''),
 isnull(Image1,''),
 isnull(UnitPrice,0),
...
FROM
 mProduct a
WHERE
 ProductID = @.ProductID
SELECT
 ID as PricingDetailID,
 isnull(PricingFromQnty,0) as PricingFromQnty,
 isnull(PricingToQnty,0) as PricingToQnty,
 isnull(RangePrice,0) as RangePrice,
 Convert(int,isnull(Status,0))as Status
FROM
 dProduct
WHERE
 ProductID = @.CategoryID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

HTH|||thks for u'r suggesion
but can u plz explain me in more detail ...
how my dataset code will look like when a single strore proc return more than one recordset.

proc must not be called more than once for that...

Regards,
Bhairav|||Sure,
Just follow the code (I'm using Microsoft Data Access Application Blocks to call):

SqlParameter [] arParms = new SqlParameter[1];
arParms[0] = new SqlParameter("@.ProductID", SqlDbType.Int);
arParms[0].Value = 1;
DataSet myDS = SQLHelper.ExecuteDataset("connectiion", StoredProcedure, "ProductDetail", arParms);
DataTable myTable1 = myDS.Tables[0];
DataTable myTable2 = myDS.Tables[1];

That should give you an example of the calling code. There are other ways to manipulate the dataset data. If you are unfamiliar, just hollar and we can give you some direction, or search the archives of the data access forms. HTH|||thks..
its really the nice way to code
thks again

Regards,
Bhairav

Is this possible?

Hello,

Is it possible to have a lable and text boxes right on the top of a parameter? Just like this.

Last: week month 90 days year

So that when a user clicks one of the link, the value in the parameter will be changed accordingly.

Sincerely,

Amde

You should be able to add textboxes with drillthrough actions that re-run the same report with different parameters.|||

hey Bob,

I am clear that I have to add textboxes with drillthrough actions. But here is the thing:

I have given a requirement to design the report layout and create the report.

the report should look like this when previewed:

Last: week month 90 days year

From: 4/25/2006

To: 5/2/2006

So for last week (when I click "week"), if I am running the report today, the From and To parameter should be populated with those values and generate the report for the specified date. Don't worry,I know how to got those values, but my question is as follow:

Assume that From and To are report parameter, how can I add the lable "Last" just on the top of the parameters as it is shown in the above. Please let me know if you have any other approach of implementing this requirement.

Sincerely,

Amde

|||The current version does not support customization of the parameter interface.

Is this possible at all for Parameters in SSRS 2000 version

Ihave 2 parameters in a report, but when i run the report would like to hide one parameter and show just one parameter on reports.

I can use the folloiwng rc:Parameters=false, but it will hide all the parameters.

I want to hode just one parameter and show the other one.

I am using sql server reporting services 2000.

Thank you very much for the information.

Try removing the string for the prompt for the one you want to hide.

Is this method possible?

Hi, I am just wondering is it possible to use report parameter values to create a view for the report?
For example I have 5 databases in SQL Server 2005: db1,db2,db3,db4 and db5. Each of the databases has the same table format but storing different datas. I need to create a report that will take in two parameters: parameter START and parameter END, lets say START=db1 and END=db4, then from these values it will create a View that contains all information from db1 up to db 5 and finally the report will be generated base on the values in the View table.
Or if anyone know of a better way to overcome this method, do let me know.
Thanks.
Hi,
I am just wondering if this question is not clear enough, there is just no answer to this particular question, or this question is being posted at the wrong section of the forum?
Please do reply something so that I know what to do.
Thanks.
|||You will need to create a stored proc that will form your desired data and output that to reporting services as a result set.|||Hi Joseph, thanks for replying. I know this method of yours will work if I only have to query from one database, but if my result set comes from a combination of a few databases, how can I create one general stored proc to do that? Thanks.
|||if the databases is on the same server, you can use the convention <database name>.dbo.<table name> for your query tables. If it's on another server, you might need to employ linked server functionalities of sql server.|||

I managed to come out with a stored proc to use the convention <database name>.dbo.<table name>. Below is the sample I used:
databases: db1, db2, db3
all has same table and column information as below:
table name: tabledb
column: data
the detail information for each of the databases are as follow:
db1: data values are 1,2,3
db2: data values are 4,5,6
db3: data values are 7,8,9
An example of the values I will have in the result set is 1,2,3,4,5,6,7,8,9 if I have parameters @.from = db1 and @.to = db3.
Below is the stored proc for it:

CREATE PROCEDURE [dbo].[GrabInfo]
@.from int, --1 as db1, 2 as db2, ...
@.to int
AS
BEGIN
SET NOCOUNT ON;

DECLARE @.sql nvarchar(MAX)
DECLARE @.temp nvarchar(17)
SET @.temp='db'+CAST(@.from AS nvarchar(1))+'.dbo.tabledb'
SELECT @.sql = 'SELECT data FROM '+@.temp
WHILE (@.from < @.to)
BEGIN
SET @.from = @.from + 1
SET @.temp = 'db' + CAST(@.from AS nvarchar(1)) + '.dbo.tabledb'
SELECT @.sql = @.sql + ' UNION SELECT data FROM '+@.temp
END
EXEC(@.sql)
RETURN
END

One more question I have, can it be done with View instead of Stored Proc?

Is there is away to pass parameter to sql script

Is there is a way to pass parameters to pass parameters to sql script during command line exeution with ISQL or OsqlPlease be more specific with example.|||thanx in advance
simple example:

i have sql script that contains the folowing :
--************
insert into table xxx values (1,2,@.PARAMETER)
--************

i am running this script with isql
my question is how i can change(replace) the parameter @.PARAMETER each time i am running the commad line with isql|||In Windows you can write a batch file and use the parameter that way. For instance, create a file (named execsql.bat for instance) and put these lines in it:

isql.exe -U user_name -P pw -S server -Q "insert into table xxx values (1,2,%1)"

pause

[You need to plug in a real username, password, and server.]

Then you can run the file from a command prompt and pass in a value such as:

execsql.bat SomeParameterValue

The "SomeParameterValue" portion will get substituted into the query.|||thanx , but this is not solving my problem because

the sql is located in sqlfile with 1000 quiries befor and 1000 quireis after . meaning :

sqlfile.sql contains the folowing :

...
...
..
insert into table xxx values (1,2,%1)
..
..
.

and i am runnig it like this :
isql.exe -U user_name -P pw -S server -i sqlfile.sql

meaning i want to pass the parameters from the batch file to the sql file.|||Ahhh. In the documentation of the isql command (Books Online), it says you can use environment variables as parameters in your query. So you might be able to do something in your batch file like:

SET ParamValue = %1
isql.exe -U user_name -P pw -S server -i sqlfile.sql

And then inside sqlfile.sql your statement would read:
...
insert into table xxx values (1,2,%ParamValue%)
...

Does that help?|||Why don't you create a stored procedure out of this script?|||sold to the man with the stored procedure !!

my suggestion exactly|||well stored procedure is brilliant idea , for sure i will use it , but this idea is not solving all the problems
look at the folowing sql that create database on sqlserver machine it needs three parameters
i can create stored procedure to do this task , but the problem is that i need to create this procedure on the MASTER database(i think) , and i dont have permissions to do it.

CREATE DATABASE @.DB_NAME on primary
(Name = eProvisionDb,
FileName = ' @.path\xxxDb.mdf',
size = 50MB,
MaxSize=UNLIMITED,
FileGrowth=10MB)
Log On
(Name = eProvisionLog,
FileName ='@.path\xxxLog.mdf',
size = 20MB,
MaxSize=UNLIMITED,
FileGrowth=5MB)
any way the stored procedure is solving 90% of my problems|||then get someone with permissions to the master database to run the create script in that context.
you can then run the procedure any time you want to.|||Hmmm, I don't think the script will run. It'll have to be dynamically built.|||sure ,
you run it in dynamic sql

Is there any way to show or hide the Document Map dynamically depend on parameters?

hi, all
I have a class report with class no as paramter.
This parameter could be null. So, if users enter null, it will show all
of them for all classes. Otherwise, it will only show the data for that
class.
So, what I am thinking about is, if the parameter is null, report shows
all data, it will be very nice to show the Document Map on the left
side. If users entered class no, then there is only one class in this
report, no use to show or use the Document map, right?
Does anyone know how to do that?One idea is to call your report through a URL and pass rc=DocMap=true or
false
Med bouchenafa
"Nick" <nick_1394@.yahoo.com.cn> a écrit dans le message de news:
1137522580.946890.33140@.f14g2000cwb.googlegroups.com...
> hi, all
> I have a class report with class no as paramter.
> This parameter could be null. So, if users enter null, it will show all
> of them for all classes. Otherwise, it will only show the data for that
> class.
> So, what I am thinking about is, if the parameter is null, report shows
> all data, it will be very nice to show the Document Map on the left
> side. If users entered class no, then there is only one class in this
> report, no use to show or use the Document map, right?
> Does anyone know how to do that?
>

Is there any way to show or hide the Document Map dynamically depend on parameters?

hi, all
I have a class report with class no as paramter.
This parameter could be null. So, if users enter null, it will show all of them for all classes. Otherwise, it will only show the data for that class.
So, what I am thinking about is, if the parameter is null, report shows all data, it will be very nice to show the Document Map on the left side. If users entered class no, then there is only one class in this report, no use to show or use the Document map, right?
Does anyone know how to do that?

If you are using the ReportViewer controls that shipped with VS 2005, you can read the values of the parameters from ReportViewer.ServerReport.GetParameters (or LocalReport depending on the mode you are using) and set ReportViewer.DocumentMapCollapsed programmatically.

If you are using url access to the server, you could use rc:DocMap=false to hide the document map, but this would require you knowing the value of the parameter beforehand, so it wouldn't work with the built-in parameter prompting.

is there any editable parameters?

i have written a function in report properties, but error returns when i
tried to add 1 to a report parameter, saying that parameter.value is
read-only.
anyways to have editable parameters'
thanks in advance~I have asked almost the same question a few days ago. Apparently no one can
tell us if this can be done.
"Jasonymk" wrote:
> i have written a function in report properties, but error returns when i
> tried to add 1 to a report parameter, saying that parameter.value is
> read-only.
> anyways to have editable parameters'
> thanks in advance~