can i use single function for a big project ??...

Hi,

I wnat to write a single function in code behind file for all DML operation, and i just want to pass query string in its parameter. but when ever my whole project will use this function to insert, update and delete. what will be the performance.

bool DMLoperation(string str){obj.conn =new SqlConnection(obj.connstring);obj.cmd =new SqlCommand(str,obj.conn);obj.conn.Open();try{if(obj.cmd.ExecuteNonQuery() > 0){obj.conn.Close();return true;}else{obj.conn.Close();return false;}}catch{obj.conn.Close();return false;}}

I am totally confused about performance, is it good idea or worst ?.

Are you going to create a SQL manager for your project?
seems it's better for you to overload SQL object other then just pass-in the sql string and execute (just my opinion)

What i can only share is my company did the same things, ie. build a customized SQL manager for SQL execution,
although performace is a key issue, you should also consider other benfit that come from this design; such as you
can run extra SQL within each SQL call, such as SQL logging;

if you build the Manager in clean and well design, i believe performance is not a problem.

Hope this help

|||

My dear, can you send some snippet of code, to use by overloading SQL object, It may be help me

Can I use keyword Like with a Parameter

Should be simple. I'm writing a report to find employees by last name.

I want the user to key in at least a few characters of the last name and the report will search for all last names that are like that.

I know in TSQL it would go:

Last_name like 'Jones%'

But how do I set up a parameter in Reporting Services

I've tried:

last_name like =@.name%

Any ideas?

Thanks,

Jim

Any ideas? Can the Keyword "Like" be used to find similar occurences of a user entered parameter...|||Within the dataset query you can declare a variable that references the parameter with the text then append the necessary % to the end. So the reference would look something like...

DECLARE @.some_variable NVARCHAR(40)
SET @.some_variable = @.parameter_field_text + '%'

You can then use this...

SELECT *
FROM some_view
WHERE NameField LIKE @.some_variable|||

You would probably need to use dynamic sql and expression.

="Select * from table1 where last_name like '" + @.name + "%'"

Or use a stored procedure and perform the like statement within it.

cheers,

Andrew

Can I use dynamic SQL in a stored procedure to call another stored

Hi,
I am having one stored procedure named SP1 which is having one input and one
output parameter as follows
CREATE PROCEDURE dbo.SP1
@.xmldoc TEXT
,@.FLAG_EXEC VARCHAR(5)
AS
BEGIN
...
END
I have write another stored procedure named SP2 which is creating a dynamic
SQL statement like
CREATE PROCEDURE dbo.SP2
AS
BEGIN
DECLARE @.CURR_PROC_NAME VARCHAR(125)
DECLARE @.STR_XML_AS_VARCHAR VARCHAR(125)
DECLARE @.STR_RET_QRY_MN VARCHAR(125)
DECLARE @.STR_EXECUTE_QRY VARCHAR(4000)
SET @.STR_EXECUTE_QRY = 'exec ' + @.CURR_PROC_NAME + ''''+
@.STR_XML_AS_VARCHAR + ''' , @.STR_RET_QRY_MN OUTPUT ,@.FLAG_EXEC = ''FALSE'' '
print @.STR_EXECUTE_QRY
EXEC (@.STR_EXECUTE_QRY)
END
but it always says to declare @.STR_RETURN_STRING variable. Can I do like
above? If yes then how? If no then why?
With thanx in advance,Hi Rajendra,
this does not work because each EXEC is running in it's own process.
That means in fact that proc2 does not know anything about the variables
you have declared in proc1.
You should handle it as follows:
Proc2 (Sub-Proc) writes the ReturnValue into a table
Proc2 (Calling Proc) reads after the run of Proc2 the value from the table
Another way is to work with "sp_executesql". See BOL for details...
HTH ;-)
Gru, Uwe Ricken
MCP for SQL Server 2000 Database Implementation
GNS GmbH, Frankfurt am Main
http://www.gns-online.de
http://www.memberadmin.de
http://www.conferenceadmin.de
________________________________________
____________
dbdev: http://www.dbdev.org
APP: http://www.AccessProfiPool.de
FAQ: http://www.donkarl.com/AccessFAQ.htm|||Rajendra (Rajendra@.discussions.microsoft.com) writes:
> I have write another stored procedure named SP2 which is creating a
> dynamic SQL statement like
> CREATE PROCEDURE dbo.SP2
> AS
> BEGIN
> DECLARE @.CURR_PROC_NAME VARCHAR(125)
> DECLARE @.STR_XML_AS_VARCHAR VARCHAR(125)
> DECLARE @.STR_RET_QRY_MN VARCHAR(125)
> DECLARE @.STR_EXECUTE_QRY VARCHAR(4000)
> SET @.STR_EXECUTE_QRY = 'exec ' + @.CURR_PROC_NAME + ''''+
> @.STR_XML_AS_VARCHAR + ''' , @.STR_RET_QRY_MN OUTPUT ,@.FLAG_EXEC = ''FALSE''
'
> print @.STR_EXECUTE_QRY
> EXEC (@.STR_EXECUTE_QRY)
> END
> but it always says to declare @.STR_RETURN_STRING variable. Can I do like
> above? If yes then how? If no then why?
You are making it too complicated. Just say:
EXEC @.CURR_PROC_NAME @.STR_XML_AS_VARCHAR, STR_RET_QRY_MN OUTPUT,
@.FLAG_EXEC = 'FALSE'
This will execute the procedure of which the name is in @.CURR_PROC_NAME.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Can I use date control for parameter in RS2000?

Can I use date control for parameter in RS2000?
--
Seeing your smile always brings me joyRS 2000 does not come with a date picker for parameters. To do this you
would have to have your own web pages and then use either URL integration or
web services to integrate with RS. RS 2005 does have a date picker control.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Macro" <Jihong.Liu@.gmail.com> wrote in message
news:5FA06B84-339D-4ED6-93FA-EE6F3E4CDCB9@.microsoft.com...
> Can I use date control for parameter in RS2000?
> --
> Seeing your smile always brings me joy|||I use date type report parameters but convert them to strings when
mapping to stored procedure parameters. E.g.,
CStr(Parameters!BeginDate.value).
Bruce L-C [MVP] wrote:
> RS 2000 does not come with a date picker for parameters. To do this you
> would have to have your own web pages and then use either URL integration or
> web services to integrate with RS. RS 2005 does have a date picker control.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Macro" <Jihong.Liu@.gmail.com> wrote in message
> news:5FA06B84-339D-4ED6-93FA-EE6F3E4CDCB9@.microsoft.com...
> > Can I use date control for parameter in RS2000?
> >
> > --
> > Seeing your smile always brings me joy

Can I sort on fields by in paramters

I need to sort a report in 3 ways 2 fields and one expression.
When i add a field to a parameter it says the following:
"Fields cannot be used in report parameter expressions"
The expression I need to sort on is this
=(sum(Fields!p1beløb.Value) - sum(Fields!p2beløb.Value)) /
(0.001+sum(Fields!p1beløb.Value))
It works if I use this as sort on the group item, but I need to choose
between 3 kinds.
How can I sort the report if I can't use fields to tell how I want it
sorted
?
Jack
--
Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
Den har indtil videre sparet mig for at få 45071 spam-mails.
Betalende brugere får ikke denne besked i deres e-mails.
Hent gratis SPAMfighter her: www.spamfighter.dkCreate a function in the code area which accepts the Parameter ( 1,2 or 3 )
and all of the fields you wish to sort on..
The function should use the parameter in a case/switch statement to choose
which value to return. In one case it should return the value of the
expression you mentioned.
In the sort column pu
=Code.GetSort(Parameters!SortVal.Value,......)
Hope this helps...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jack Nielsen" <no_spam jack.nielsen@.get2net.dk> wrote in message
news:ebEGvm9tFHA.1472@.TK2MSFTNGP15.phx.gbl...
>I need to sort a report in 3 ways 2 fields and one expression.
> When i add a field to a parameter it says the following:
> "Fields cannot be used in report parameter expressions"
> The expression I need to sort on is this
> =(sum(Fields!p1beløb.Value) - sum(Fields!p2beløb.Value)) /
> (0.001+sum(Fields!p1beløb.Value))
> It works if I use this as sort on the group item, but I need to choose
> between 3 kinds.
> How can I sort the report if I can't use fields to tell how I want it
> sorted
> ?
> Jack
>
>
> --
> Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
> Den har indtil videre sparet mig for at få 45071 spam-mails.
> Betalende brugere får ikke denne besked i deres e-mails.
> Hent gratis SPAMfighter her: www.spamfighter.dk
>|||I think this is somehow difficult, doesn't other designers need to sort the
result differently based upon fields ?
Jack
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> skrev i en meddelelse
news:eZbuG$DuFHA.3740@.TK2MSFTNGP14.phx.gbl...
> Create a function in the code area which accepts the Parameter ( 1,2 or
3 )
> and all of the fields you wish to sort on..
> The function should use the parameter in a case/switch statement to choose
> which value to return. In one case it should return the value of the
> expression you mentioned.
> In the sort column pu
> =Code.GetSort(Parameters!SortVal.Value,......)
> Hope this helps...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Jack Nielsen" <no_spam jack.nielsen@.get2net.dk> wrote in message
> news:ebEGvm9tFHA.1472@.TK2MSFTNGP15.phx.gbl...
> >I need to sort a report in 3 ways 2 fields and one expression.
> >
> > When i add a field to a parameter it says the following:
> >
> > "Fields cannot be used in report parameter expressions"
> >
> > The expression I need to sort on is this
> > =(sum(Fields!p1beløb.Value) - sum(Fields!p2beløb.Value)) /
> > (0.001+sum(Fields!p1beløb.Value))
> >
> > It works if I use this as sort on the group item, but I need to choose
> > between 3 kinds.
> >
> > How can I sort the report if I can't use fields to tell how I want it
> > sorted
> > ?
> >
> > Jack
> >
> >
> >
> >
> > --
> > Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
> > Den har indtil videre sparet mig for at få 45071 spam-mails.
> > Betalende brugere får ikke denne besked i deres e-mails.
> > Hent gratis SPAMfighter her: www.spamfighter.dk
> >
> >
>

Can I See What A Parameter is Passing to a Sproc?

I've got a report with 4 parameters to a stored procedure. The parameters
all work in the stored procedure when I run it in Query Analyzer, but only
one of them works in the report.
Is there a way to see what's being passed to the sproc? I don't have admin
rights to the DB so I can't use the profiler.
Any troubleshooting ideas for parameters would be welcome.
Thanks,
LizSince you cannot access the database, what you can do is first you can give
litral values for the stored proc in the Data tab and see whether it works
and remove one by one and see whether it works..
I feel the problem is due to some Null values which may not have been
handled in the stored proc.
Amarnath
"Liz" wrote:
> I've got a report with 4 parameters to a stored procedure. The parameters
> all work in the stored procedure when I run it in Query Analyzer, but only
> one of them works in the report.
> Is there a way to see what's being passed to the sproc? I don't have admin
> rights to the DB so I can't use the profiler.
> Any troubleshooting ideas for parameters would be welcome.
> Thanks,
> Liz|||Everything works fine in the Data tab. However, it fails in the Preview
tab. To test the parameters, I tweaked the sproc to only return what it had
been sent in parameters. I created another report to display what it had
sent to the sproc. Everything checks out. The report is sending what it's
supposed to. So now I'm back to the original report.
How can I troubleshoot the Preview? I suspect there's something in the
stored procedure that it doesn't like but that would be hard to narrow down.
The other thing might be that I orignally created this sproc and report with
one parameter - the one that works. I then added the other three - they
don't work. Do you have add all the parameters at the outset? Can you not
add parameters later?
What kind of null values are you talking about? Null values in the data?
Or null values in the parameters? There are no nulls in the parameters. I
will now go and make sure I'm not sending any nulls back to RS.
Thanks.
"Amarnath" wrote:
> Since you cannot access the database, what you can do is first you can give
> litral values for the stored proc in the Data tab and see whether it works
> and remove one by one and see whether it works..
> I feel the problem is due to some Null values which may not have been
> handled in the stored proc.
> Amarnath
> "Liz" wrote:
> > I've got a report with 4 parameters to a stored procedure. The parameters
> > all work in the stored procedure when I run it in Query Analyzer, but only
> > one of them works in the report.
> >
> > Is there a way to see what's being passed to the sproc? I don't have admin
> > rights to the DB so I can't use the profiler.
> >
> > Any troubleshooting ideas for parameters would be welcome.
> >
> > Thanks,
> > Liz|||I just went and bulletproofed every field to make sure that a null would not
be returned to RS and that, unfortunately, made no difference.
I'm desperate for any ideas you or anyone might have.
Thanks.
"Amarnath" wrote:
> Since you cannot access the database, what you can do is first you can give
> litral values for the stored proc in the Data tab and see whether it works
> and remove one by one and see whether it works..
> I feel the problem is due to some Null values which may not have been
> handled in the stored proc.
> Amarnath
> "Liz" wrote:
> > I've got a report with 4 parameters to a stored procedure. The parameters
> > all work in the stored procedure when I run it in Query Analyzer, but only
> > one of them works in the report.
> >
> > Is there a way to see what's being passed to the sproc? I don't have admin
> > rights to the DB so I can't use the profiler.
> >
> > Any troubleshooting ideas for parameters would be welcome.
> >
> > Thanks,
> > Liz|||My apologies if this is a double-post but I just bulletproofed all the fields
to make sure that no nulls are returned to RS but that didn't make any
difference.
So I'm still searching for ideas on this one.
"Amarnath" wrote:
> Since you cannot access the database, what you can do is first you can give
> litral values for the stored proc in the Data tab and see whether it works
> and remove one by one and see whether it works..
> I feel the problem is due to some Null values which may not have been
> handled in the stored proc.
> Amarnath
> "Liz" wrote:
> > I've got a report with 4 parameters to a stored procedure. The parameters
> > all work in the stored procedure when I run it in Query Analyzer, but only
> > one of them works in the report.
> >
> > Is there a way to see what's being passed to the sproc? I don't have admin
> > rights to the DB so I can't use the profiler.
> >
> > Any troubleshooting ideas for parameters would be welcome.
> >
> > Thanks,
> > Liz|||>>
Do you have add all the parameters at the outset? Can you not
add parameters later?
<<
Yes you can definitely add parameters later. However sometimes there is
something subtly different in the *way* you added the other three that might
be making a difference.
I would look at the code (the XML) instead of trying to handle this in the
Designer. Take a look at all of the parameter definitions and find out what
makes that one different from the others.
There also might be something different about the params in the sproc -- for
example, do they all have default values, or only one/some?
FWIW -- in your other message you say you " just bulletproofed all the
fields to make sure that no nulls are returned to RS" but I don't think
Amarnath was concerned about nulls RETURNED to RS. I think s/he was
concerned about the way the *sproc* might handle a null. I know you said
that there are no nulls in the params you are sending -- just clarifying.
Is there any possibility we have a type or casting issue here?
>L<
"Liz" <Liz@.discussions.microsoft.com> wrote in message
news:D64A0349-6597-4A1D-9C09-51DE7B9FD0BA@.microsoft.com...
> Everything works fine in the Data tab. However, it fails in the Preview
> tab. To test the parameters, I tweaked the sproc to only return what it
> had
> been sent in parameters. I created another report to display what it had
> sent to the sproc. Everything checks out. The report is sending what
> it's
> supposed to. So now I'm back to the original report.
> How can I troubleshoot the Preview? I suspect there's something in the
> stored procedure that it doesn't like but that would be hard to narrow
> down.
> The other thing might be that I orignally created this sproc and report
> with
> one parameter - the one that works. I then added the other three - they
> don't work. Do you have add all the parameters at the outset? Can you
> not
> add parameters later?
> What kind of null values are you talking about? Null values in the data?
> Or null values in the parameters? There are no nulls in the parameters.
> I
> will now go and make sure I'm not sending any nulls back to RS.
> Thanks.
> "Amarnath" wrote:
>> Since you cannot access the database, what you can do is first you can
>> give
>> litral values for the stored proc in the Data tab and see whether it
>> works
>> and remove one by one and see whether it works..
>> I feel the problem is due to some Null values which may not have been
>> handled in the stored proc.
>> Amarnath
>> "Liz" wrote:
>> > I've got a report with 4 parameters to a stored procedure. The
>> > parameters
>> > all work in the stored procedure when I run it in Query Analyzer, but
>> > only
>> > one of them works in the report.
>> >
>> > Is there a way to see what's being passed to the sproc? I don't have
>> > admin
>> > rights to the DB so I can't use the profiler.
>> >
>> > Any troubleshooting ideas for parameters would be welcome.
>> >
>> > Thanks,
>> > Liz|||Liz wrote:
> I've got a report with 4 parameters to a stored procedure. The parameters
> all work in the stored procedure when I run it in Query Analyzer, but only
> one of them works in the report.
> Is there a way to see what's being passed to the sproc? I don't have admin
> rights to the DB so I can't use the profiler.
> Any troubleshooting ideas for parameters would be welcome.
> Thanks,
> Liz
Aside from the SQL Profiler (as you mentioned), I would suggest
creating a stored procedure that just accepts the 4 parameter values
and inserts them into a basic table with 4 varchar/nvarchar defined
columns. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||On Apr 24, 11:42 am, Liz <L...@.discussions.microsoft.com> wrote:
> I've got a report with 4 parameters to a stored procedure. The parameters
> all work in the stored procedure when I run it in Query Analyzer, but only
> one of them works in the report.
> Is there a way to see what's being passed to the sproc? I don't have admin
> rights to the DB so I can't use the profiler.
> Any troubleshooting ideas for parameters would be welcome.
> Thanks,
> Liz
Oh, I forgot, you could also create 4 textbox controls in the report
and add an expression similar to this for each one that way it will
display the values in the report itself.
=Parameters!Parameter1Name.Value
Also, if the SP is erroring out, temporarily replace it with a basic
query that definitely works. Hope this is helpful.
Regards,
Enrique Martinez
Sr. Software Consultant

Can I Restrict Values in a Parameter Dropdown based on the User?

I've got a puzzler here. I have a report with 4 different dropdown lists for
parameters. One of them is Department Id. For some users, I want them to be
able to only run the report against their own department id but still be able
to select any of the values for the other parameters. Other users will be
able to select any value for department id and the other parameters.
I can figure out how to write the query to populate the Department ID
dropdown but cannot figure how to access the current userid programatically.
Is there a way to access the user ID in order to restrict the values in a
parameter dropdown?
I'm using RS2003 with SQL Server 2000. Any ideas are welcome!
Thanks,
LizThis is really quite easy. There is a global parameter called User!UserID it
returns the domain and user ID of the user running the report. To see the
format of this create a report with a single textbox (no datasets). Set the
text box to an expression and set it to this (case sensitive):
=User!UserID
You might want to strip the domain off.
OK, the second part of this is that you need to realize that query parameter
and report parameter are two different things. You do not have to have a
query parameter mapped to a report parameter. It can be mapped to an
expression instead.
So, let's say you have this query
Select department from mydepartmentlist where userID = @.UserID
RS will create a report parameter for this called UserID. You will not use
it. You will delete it. Instead in the dataset tab click on the ... go to
the parameters tab, click on the dropdown box where the query parameter is
being mapped to report parameter and pick expression instead. Then set it to
the expression above. If you don't want the domain then use something like
this:
=mid(User!UserID, LengthToCutOffTheFront)
Of course for all of this to work you need a table somewhere that maps the
userid to the departments.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Liz" <Liz@.discussions.microsoft.com> wrote in message
news:5A54A060-B916-4BB4-AE03-9AF8302601A7@.microsoft.com...
> I've got a puzzler here. I have a report with 4 different dropdown lists
> for
> parameters. One of them is Department Id. For some users, I want them to
> be
> able to only run the report against their own department id but still be
> able
> to select any of the values for the other parameters. Other users will be
> able to select any value for department id and the other parameters.
> I can figure out how to write the query to populate the Department ID
> dropdown but cannot figure how to access the current userid
> programatically.
> Is there a way to access the user ID in order to restrict the values in a
> parameter dropdown?
> I'm using RS2003 with SQL Server 2000. Any ideas are welcome!
> Thanks,
> Liz
>|||Thanks! That's very helpful and I've already succeeded at stripping off the
domain name.
I know you can pass parameter values through the URL and query string to the
report. If I wanted to pass the department ID from the calling application
to populate the dropdown, is there a way to pass that one parameter value and
let the user select the other three?
Thanks for your help,
Liz
"Bruce L-C [MVP]" wrote:
> This is really quite easy. There is a global parameter called User!UserID it
> returns the domain and user ID of the user running the report. To see the
> format of this create a report with a single textbox (no datasets). Set the
> text box to an expression and set it to this (case sensitive):
> =User!UserID
> You might want to strip the domain off.
> OK, the second part of this is that you need to realize that query parameter
> and report parameter are two different things. You do not have to have a
> query parameter mapped to a report parameter. It can be mapped to an
> expression instead.
> So, let's say you have this query
> Select department from mydepartmentlist where userID = @.UserID
> RS will create a report parameter for this called UserID. You will not use
> it. You will delete it. Instead in the dataset tab click on the ... go to
> the parameters tab, click on the dropdown box where the query parameter is
> being mapped to report parameter and pick expression instead. Then set it to
> the expression above. If you don't want the domain then use something like
> this:
> =mid(User!UserID, LengthToCutOffTheFront)
> Of course for all of this to work you need a table somewhere that maps the
> userid to the departments.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Liz" <Liz@.discussions.microsoft.com> wrote in message
> news:5A54A060-B916-4BB4-AE03-9AF8302601A7@.microsoft.com...
> > I've got a puzzler here. I have a report with 4 different dropdown lists
> > for
> > parameters. One of them is Department Id. For some users, I want them to
> > be
> > able to only run the report against their own department id but still be
> > able
> > to select any of the values for the other parameters. Other users will be
> > able to select any value for department id and the other parameters.
> >
> > I can figure out how to write the query to populate the Department ID
> > dropdown but cannot figure how to access the current userid
> > programatically.
> > Is there a way to access the user ID in order to restrict the values in a
> > parameter dropdown?
> >
> > I'm using RS2003 with SQL Server 2000. Any ideas are welcome!
> >
> > Thanks,
> > Liz
> >
> >
>
>|||Sure. You have have a hidden parameter that gets filled in by the calling
application. The user only selects from the list you provide. Look at hidden
parameters in the books online.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Liz" <Liz@.discussions.microsoft.com> wrote in message
news:D0F372BF-DA85-4BD6-A01C-ED2708F14BAC@.microsoft.com...
> Thanks! That's very helpful and I've already succeeded at stripping off
> the
> domain name.
> I know you can pass parameter values through the URL and query string to
> the
> report. If I wanted to pass the department ID from the calling
> application
> to populate the dropdown, is there a way to pass that one parameter value
> and
> let the user select the other three?
> Thanks for your help,
> Liz
>
> "Bruce L-C [MVP]" wrote:
>> This is really quite easy. There is a global parameter called User!UserID
>> it
>> returns the domain and user ID of the user running the report. To see the
>> format of this create a report with a single textbox (no datasets). Set
>> the
>> text box to an expression and set it to this (case sensitive):
>> =User!UserID
>> You might want to strip the domain off.
>> OK, the second part of this is that you need to realize that query
>> parameter
>> and report parameter are two different things. You do not have to have a
>> query parameter mapped to a report parameter. It can be mapped to an
>> expression instead.
>> So, let's say you have this query
>> Select department from mydepartmentlist where userID = @.UserID
>> RS will create a report parameter for this called UserID. You will not
>> use
>> it. You will delete it. Instead in the dataset tab click on the ... go to
>> the parameters tab, click on the dropdown box where the query parameter
>> is
>> being mapped to report parameter and pick expression instead. Then set it
>> to
>> the expression above. If you don't want the domain then use something
>> like
>> this:
>> =mid(User!UserID, LengthToCutOffTheFront)
>> Of course for all of this to work you need a table somewhere that maps
>> the
>> userid to the departments.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Liz" <Liz@.discussions.microsoft.com> wrote in message
>> news:5A54A060-B916-4BB4-AE03-9AF8302601A7@.microsoft.com...
>> > I've got a puzzler here. I have a report with 4 different dropdown
>> > lists
>> > for
>> > parameters. One of them is Department Id. For some users, I want them
>> > to
>> > be
>> > able to only run the report against their own department id but still
>> > be
>> > able
>> > to select any of the values for the other parameters. Other users will
>> > be
>> > able to select any value for department id and the other parameters.
>> >
>> > I can figure out how to write the query to populate the Department ID
>> > dropdown but cannot figure how to access the current userid
>> > programatically.
>> > Is there a way to access the user ID in order to restrict the values in
>> > a
>> > parameter dropdown?
>> >
>> > I'm using RS2003 with SQL Server 2000. Any ideas are welcome!
>> >
>> > Thanks,
>> > Liz
>> >
>> >
>>

Can I pass ResultSet as parameter of Stored Proc directly ?

Hi, All
Is it possible for me that I pass a resultset as parameter of Stored Proc
directly instead of putting it to a table before calling a stored procedure
which manipulate data of the table?
Thanks a lot in advance for your answer.
http://www.sommarskog.se/share_data.html
http://www.aspfaq.com/
(Reverse address to reply.)
"Sally" <Sally@.discussions.microsoft.com> wrote in message
news:1FBF91FA-172B-41AC-8834-0C7AAE52310D@.microsoft.com...
> Hi, All
> Is it possible for me that I pass a resultset as parameter of Stored Proc
> directly instead of putting it to a table before calling a stored
procedure
> which manipulate data of the table?
> Thanks a lot in advance for your answer.

Can I pass ResultSet as parameter of Stored Proc directly ?

Hi, All
Is it possible for me that I pass a resultset as parameter of Stored Proc
directly instead of putting it to a table before calling a stored procedure
which manipulate data of the table?
Thanks a lot in advance for your answer.http://www.sommarskog.se/share_data.html
http://www.aspfaq.com/
(Reverse address to reply.)
"Sally" <Sally@.discussions.microsoft.com> wrote in message
news:1FBF91FA-172B-41AC-8834-0C7AAE52310D@.microsoft.com...
> Hi, All
> Is it possible for me that I pass a resultset as parameter of Stored Proc
> directly instead of putting it to a table before calling a stored
procedure
> which manipulate data of the table?
> Thanks a lot in advance for your answer.

Can I pass ResultSet as parameter of Stored Proc directly ?

Hi, All
Is it possible for me that I pass a resultset as parameter of Stored Proc
directly instead of putting it to a table before calling a stored procedure
which manipulate data of the table?
Thanks a lot in advance for your answer.http://www.sommarskog.se/share_data.html
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Sally" <Sally@.discussions.microsoft.com> wrote in message
news:1FBF91FA-172B-41AC-8834-0C7AAE52310D@.microsoft.com...
> Hi, All
> Is it possible for me that I pass a resultset as parameter of Stored Proc
> directly instead of putting it to a table before calling a stored
procedure
> which manipulate data of the table?
> Thanks a lot in advance for your answer.

can i make optional OUTPUT paramater?

when i calling stored procedure
can i make one of the output parameter be optional?
not sent the parater to the procedure
Tnx in advanceJust give it a default value of NULL
procname
(
@.p1 INT = NULL OUTPUT
) AS
BEGIN
...
END|||> when i calling stored procedure
> can i make one of the output parameter be optional?
>
sure. just give the parameter a default value e.g.
create procedure dbo.proc
(
@.value varchar(10)=null output
)
as
...
James