.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

SQL Server 2005 - Specifying Optional Parameter in Stored Procedure

Posted By:      Posted Date: September 21, 2010    Points: 0   Category :Sql Server
I need to alter the following stored procedure so that the UserID is an optional parameter. I have, as yet, been unsuccessful. Basically, if the user doesn't supply a User ID, I want to return all of the users that match the other criteria.

This seems like it should be a simple thing.......


@CompanyID tinyint

,@DepartmentID tinyint

,@ApplicationID int

,@UserID AS









FROM [ReportQueries]


[UserID] = @UserID

View Complete Post

More Related Resource Links

optional output parameter in CLR Stored Procedure

I'm trying to transpose a Procedure that sends mail via CDO.Message and Ole Automation to a .Net CLR Procedure. this procedure must have the same parameters in the same order as the original to maintain compatibility with everything that already uses it. the declaration goes as follow   <Microsoft.SqlServer.Server.SqlProcedure(name:="SP_ENVIAEMAIL")> _<br/> Public Shared Sub ENVIAEMAIL(<SqlFacet(MaxSize:=100)> Optional ByVal De As String = "", <SqlFacet(MaxSize:=100)> Optional ByVal Para As String = "", _<br/> <SqlFacet(MaxSize:=100)> Optional ByVal Copia As String = "", <SqlFacet(MaxSize:=100)> Optional ByVal Oculta As String = "", _<br/> <SqlFacet(MaxSize:=100)> Optional ByVal Assunto As String = "", <SqlFacet(MaxSize:=-1)> Optional ByVal Mensagem As String = "", _<br/> <SqlFacet(MaxSize:=-1)> Optional ByVal Anexo As String = "", _<br/> <strong><SqlFacet(MaxSize:=1000)> <Out()> Optional ByVal Retorno As String = ""</strong> , _<br/> <SqlFacet(MaxSize:=100)> Optional ByVal Smtp As String = "", <SqlFacet(MaxSize:=100)> Optional ByVal Usuario As String = "",

SQL Server 2008 Stored Procedure output parameter

I would like to get OUTPUT parameter names from stored procedure without executing stored procedure.  for excample schema API will help to get input parameter metadata. Thanks in advance. Murali

how to create stored procedure in sql server 2005 and how to use that stored procedure in asp.net


Hi friends ,

       I am very newbie

I have created stored procedure and student database and also asp.net application for asp.net page

but it could not found stored procedure what is the mistake actually I don't no

Please help me

Warm Regards


CRUD Stored Procedure Code/Scripts Generator for SQL SERVER 2005


I need a simple anf functionally CRUD Stored Procedure Code/Scripts Generator.

Anyone have a solution?

Thanks in Advance.

SQL Server 2005 Grant User Permission to Create Stored Procedure and Grant Execute Access to another


I'm trying to determine the minimal amount of access I can give my developers to be able to run create stored procedure scripts with an inclusion of the grant execute to the application user group.

We have been developing some interesting work arounds, but I think there must be an easier way to grant the access.

Grant Create Stored Procedure to user1 on a specific database

Grant Permission to Grant Execute permission for Stored Procedures on a specific Database to user1 for any user.


SQL Server 2005 Transactional Replication Fails to Publish Stored Procedure Containing an Index Crea


I've experienced a bizarre problem with a SQL Server 2005 Transactional Publication. The issue is this: If the publication contains an article that is a stored procedure that contains a create index statement, then there is an error thrown when attempting to replicate the schema of the stored procedure to a subscriber.

The behavior is very odd, because even if the create index statement is commented out, it still gives the exception, and it will only work if it is removed altogether.

Here is the exact error that's being returned:

Command attempted: GRANT EXECUTE ON [dbo].[usp_Test] TO [CompanyDatabase_access]

(Transaction sequence number: 0x00000170000008B9000500000000, Command ID: 5)

Error messages: Cannot find the object 'usp_Test', because it does not exist or you do not have permission. (Source: MSSQLServer, Error number: 15151) Get help: http://help/15151 Cannot find the object 'usp_Test', because it does not exist or you do not have permission. (Source: MSSQLServer, Error number: 15151) Get help: http://help/15151

The error is accurate, because when I check on the subscriber, the stored procedure wasn't created as expected... but that was the purpose of the publication...

Calling stored procedure from funciton in sql server 2005


Is there any way to call stored procedure from funciton in sql server 2005?

If it is possible, please give some examples.



RAHUL Dot Net Developer

Send Email from SQL Server Express Using a CLR Stored Procedure

One of the nice things about SQL Server is the ability to send email using T-SQL. The downside is that this functionality does not exist in SQL Server Express. In this tip I will show you how to build a basic CLR stored procedure to send email messages from SQL Server Express, although this same technique could be used for any version of SQL Server.

If you have not yet built a CLR stored procedure, please refer to this tip for what needs to be done for the initial setup.

Sort by gridview SortExpression parameter via Stored Procedure


I have a gridview that calls data via a stored procedure.  I am unable to enable the gridview columns to be sortable. I need to set the parameter in the Stored Procedure, can someone help me with this?

Here is my gridview:

<asp:GridView ID="AllUsersGrid" runat="server" AutoGenerateColumns="False" DataKeyNames="UserName"
                        GridLines="Vertical" Width="900px" DataSourceID="SqlDataSource1" AllowSorting="True"
                        SelectedRowStyle-Height="30px" CellPadding="4" BackColor="White">
                            <asp:TemplateField HeaderText="Full Name" SortExpression="lastname">
                                    <asp:Label ID="DisplayName" runat="server" Text='<%# Eval("firstname").ToString() & " " & Eval("lastname").ToString() %>' />
                            <asp:BoundField HeaderText="User Name" DataField="UserName" />

TSQL: Passing array/list/set to stored procedure (MS SQL Server)

Passing array/list/set to stored procedure is fairly common task when you are working with Databases. You can meet this when you want to filter some collection. Other case - it can be an import into database from extern sources. I will consider few solutions: creation of sql-query at server code, put set of parameters to sql stored procedure's parameter with next variants: parameters separated by comma, bulk insert, and at last table-valued parameters (it is most interesting approach, which we can use from MS SQL Server 2008). Ok, let's suppose that we have list of items and we need to filter this items by categories ("TV", "TV game device", "DVD-player") and by firms ("Firm 1", "Firm2", "Firm 3). It will look at database like this So we need a query which will return us list of items from database. Also we need opportunity to filter these items by categories or by firms. We will filter them by identifiers. Ok, we know the mission. How we will solve it? Most easy way, used by junior developers - it is creating SQL-instruction with C# code, it can be like this List<int> categories = new List<int>() { 1, 2, 3 };   StringBuilder sbSql = new StringBuilder(); sbSql.Append( @" select i.Name as ItemName, f.Name as FirmName, c.Name as CategoryName from Item i inner join Firm f on i.FirmId =

Using IN clause(string parameter) in Stored Procedure

Hi guys. I need to use IN clause in a selection query. From all the resources that I found from google, it's all about adding a function to split a integer parameter into a list. But I got a string parameter, for example 'A1,B1,C1' I tried to use the below where single quotes in @CustCode is double up. ___________________________________ Declare @SQL VarChar(1000)   SELECT @SQL = ' SELECT [something]' SELECT @SQL = @SQL + ' FROM [Table]' SELECT @SQL = @SQL + ' WHERE CustomerCode IN ('  + @CustCode + ')'   EXECUTE (@SQL) _____________ Executing the stored procedure in MSSQL works fine, but it didn't when executing from Visual Studio 2005. Please advice.

problem for using webservice (sql parameter stored Procedure)

i want to put my database layer in the web service. This works fine BUT there is a problem when i am access these methods GetSPData(Byval arg As SqlComman, argCmdText as string, ByVal argSqlConnection as SqlConnection) As DataTable I want to acces the aforesaid method in my Desktop application and i have to send SqlCommand as parament. When i try to acces this (GetSPData) method the follow exception occured "There was an error generating the XML document." Now the problem is to converting DataTable in xmlDocument Or xml node (i dont know about it very much). Can anyone tell me How to send SqlCommand as parament ?? How to convert DataTable into XmlDocument?? When i try to test this method the following Error occurs " test form is only available for methods with primitive types as parameters. " The following are my Web Service Method code in which i try to Get Data Using Stored Procedure and then convert DataTable into XmlDocument and return. Dim XmlDoc As New XmlDocument Dim ds As New DataSet Dim oDBLibrary As New DataBaseLibrary.DatabaseClass Dim dt As DataTable = oDBLibrary.GetSPData(objCommand1, cmdText, msqlConnection) ds.Tables.Add(dt) Dim sw As New StringWriter ds.Tables(0).WriteXml(sw) sw.Close() Dim tableXml As String = sw.ToString XmlDoc.Load(tableXml) Return XmlDoc  Now can anyone tell me what is wrong with that c

stored procedure with parameter quest

I can execute this statement fine: SELECT item,cust_name,OFFICE from sample where cust_name = @p1 but what if I wanted to use "where cust_name like @p1" ? It does not seem to give any results nor error. I have tried "where cust_name like '%' + @p1 + '%' to no avail

Launching a stored procedure with parameter from Excel 2003

I have a stored procedure in SQL Server which needs a parameter and returns a resultset.   sp_xxx 'parameterValue'   I'd like to know if it is possible to launch this stored procedure through Excel 2003 and get the resultset in the active spreadsheet.   I have tried to do this with Microsoft Query but it doesn't allow parameters in queries that can't be graphically represented.   I have tried also through an ODC files but I get an error.   Is it possible to do this?

SQL Server 2005 Express Edition - GUI to set permissions on stored procedures

Hi there, I have SQL Serve 2005 Express Edition (Build 2600: Service Pack 3) installed; I also have the Management Console installed. My problem is that I cannot set execute permission, or any other type of permissions on my stored procedures through the GUI, as the Property menu item is missing from the right click menu. I read somewhere that this happens when you have SP1, but as I stated above I have got SP3 installed... Any help?   Regards, D.

SQL Server 2005 Stored Procedures

  I had written a stored proc. to get date & time in Std. format  dd/mm/yy hh:mm:ss AM/PM. I wanted that  when no value is given to the stored procedure it should produce current date & time in the above std. format & when some date is given , it should display that date in above std. format. The stored proc. is as follows: set  ANSI_NULLS ON set  QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[GETDATETIME] @DATE DATETIME = NULL, @NEWDATE VARCHAR(MAX) OUTPUT AS BEGIN --DECLARE @TEMPDATE DATETIME; --SET @TEMPDATE=NULL; --SET @TEMPDATE=; IF (@DATE IS NULL) BEGIN SET @DATE=GETDATE(); SET @DATE=CONVERT(DATETIME,CONVERT(VARCHAR(MAX),@DATE,3)+ ' ' + CONVERT(VARCHAR(MAX),@DATE,24)) ; SET @NEWDATE = convert(varchar(MAX),@DATE, 103)+' ' + CONVERT(VARCHAR(MAX),SUBSTRING(CONVERT(varchar(MAX),@DATE,22),10,12)); GOTO L1; END SET @DATE=CONVERT(DATETIME,CONVERT(VARCHAR(MAX),@DATE,3)+ ' ' + CONVERT(VARCHAR(MAX),@DATE,24)) ; SET @NEWDATE = convert(varchar(MAX),@DATE, 103)+' ' + CONVERT(VARCHAR(MAX),SUBSTRING(CONVERT(varchar(MAX),@DATE,22),10,12)); -- Declare the return variable here --DECLARE @Result int -- Add the T-SQL statements to compute the return value here --SELECT @Result = @p1 -- Return the result of the function --SET @NEWDATE2=CONVERT(VARCHAR(MAX),@NEWDATE); L1: RETURN CONVERT(VARCHAR(MAX),@N

BIDS 2005 cannot find Stored Procedure

I have read how to do this in multiple places but my report is not able to use a stored procedure for retrieving data. When I select command type 'stored procedure' to populate my dataset, and then switch to the GUI query designer, I only see a partial list of system SPs, not my user defined SPs. My SP does return a single dataset.If I try to manually use the SP, with or without doublequotes and EXEC, the report designer simply tells me that the SP cannot be found.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend