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

Post New Web Links

Send NULL value to a stored procedure variable

Posted By:      Posted Date: September 26, 2010    Points: 0   Category :ASP.Net

Hi all. I want to send an image to a stored procedure variable that is varbinary(max) field, It works correctly when an image selected by fileupload and my image property has image file, and it can send to stored procedure. but when fileupload is empty I want to send NULL to stored procedure variable that is varbinary(max). I use DbNull.Value in C# code. But error occurs. What should I do for send Null to stored procedure? Cry

View Complete Post

More Related Resource Links

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.

How can i send an input param of XML with more than 8K to a stored procedure in ADO?

 My sample SP : CREATE PROCEDURE MyINV   @sData AS XML  AS  BEGIN  SET NOCOUNT ON; select 1 as iTestCount  END The param sData is more than 8000 characters.  till 8000 bytes, it works .But throwing exception on 8001 bytes.   Code = 80040e21,Code meaning = IDispatch error #3105,Source = Microsoft OLE DB Provider for ODBC Drivers,Description = [Microsoft][ODBC SQL Server Driver]String data, right truncation   Im using VC++/ADO. _variant_t varChunk; varChunk = (char*)(sValue.GetBuffer()); pParam->AppendChunk(varChunk); pCommand->Parameters->Append(pParam); pCommand->Execute(...)  

Can I pass a value for a output variable in a stored procedure in ADO.NET?

I am calling a stored procedure, that exists in a SQL Server 2005 database,  using ADO.NET, in which I instantiate a sql command object.The stored procedure has an ouput variable like '@packageCode VARCHAR(100) = NULL OUTPUT'.Can I set it's value when creating the sql command object? Or I can never set a value for an output type variable in  a stored procedure?

Stored Procedure utilizing table variable

I'm creating a stored procedure that will take the results of a temp table and check for existence using the IN keyword.  The code looks like this: ALTER PROCEDURE [dbo].[studentBranchLogin] -- Add the parameters for the stored procedure here @userName varchar(50), @userPass varchar(20) AS BEGIN DECLARE @dActive bit; DECLARE @TT TABLE (dept int, active bit); -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; INSERT INTO @TT(dept, active) SELECT deptid, active FROM srcuDeptId WHERE active = TRUE -- Insert statements for procedure here SELECT srcuid FROM srcuLogins WHERE logon = @userName AND password = @userPass AND srcuid IN @TT END I get a Msg 170, Level 15 state 1 Line 24: Incorrect syntax near '@TT'. So while I'm sure the @TT temp table isn't being read, I find very few resources for answer what the proper syntax should be.   Any assistance is greatly appreciated.

Passing Table Variable to Stored Procedure

My stored procedure expects a uniqueidentifier as a parameter.  However, this unique identifier is stored in a table variable, which looks like this:DECLARE @TableGiftGuid table ( GiftGuid uniqueidentifier )When I try to execute a stored procedure by passing the GiftGuid like so:EXEC my_procedure (SELECT GiftGuid FROM @TableGiftGuid) I get an error. What I ended up doing is declaring another variable, storing the GiftGuid into it and then passing the variable to the stored procedure like this: DECLARE @TableGiftGuid table ( GiftGuid uniqueidentifier ) DECLARE @GiftGuid uniqueidentifieDECLARE @TableGiftGuid table ( GiftGuid uniqueidentifier ) DECLARE @GiftGuid uniqueidentifier SET @GiftGuid = (SELECT GiftGuid FROM @TableGiftGuid) EXEC my_procedure @GiftGuidThat works, but is there a more elegant way of doing this?

Stored Procedure, when to use Output paramemter vs Return variable


When would you use an output parameter vs a return variable, or vice versa? In the following simple example, I can achieve the samething using either one.

Using output parameter

create proc dbo.TestOutput (@InValue int, @OutValue int output)
set @OutValue = @InValue

declare @x int
exec TestOutput @InValue = 3, @OutValue = @x output
select @x


Using return variable:

create proc dbo.TestReturn (@InValue int)
return @InValue

declare @x int
exec @x = TestReturn @InValue = 3
select @x


As you can see, they both do the samething. Can someone show me an example where the choice of a output parameter vs a return variable would make a difference?

Two results from two different tables by Stored Procedure and put them in variables and send email.


Hi All, first i had like 7-8 steps just to execute stored proc and send email. now i have put everything in one stored proc as follows :

Alter procedure PlanFinder.InsertInvalidRecords

Truncate table [PlanFinder].[InvalidAwps] 
INSERT INTO [PlanFinder].[InvalidAwps](Ndc, AwpUnitCost) 

SELECT DISTINCT P.Ndc Ndc, A.Price AwpUnitCost 
    PlanFinder.PlanFinder.HpmsFormulary P 
    LEFT JOIN (SELECT Ndc, Price FROM MHSQL01D.Drug.FdbPricing.vNdcPrices  
               WHERE PriceTypeCode = '01' AND CurrentFlag = 1) A 
ON P.Ndc = A.Ndc  
WHERE (A.Ndc IS NULL OR A.Price <= 0 OR A.Price IS NULL) 

DELETE FROM PlanFinder.NdcAwp
       Ndc IN
              SELECT Ndc
              FROM  PlanFinder.InvalidAwps                     &nb

C# How to check prammatically if null value exists in database table (using stored procedure)?


How to programattically check if null value exists in database table (using stored procedure)?

I know it's possble in the Query Analyzer (see last SQL query batch statements)?

But how can I pass null value as parameter to the database stored procedure programattically using C#?

Although I can check for empty column (the following code passes string.Empty as parameter but how to pass null value?), I cannot check for null value in the following code snippet:

SQL Queries:

USE [master]

IF EXISTS (SELECT name FROM sys.databases WHERE name = 'ExampleDatabase')
DROP DATABASE [ExampleDatabase];

CREATE DATABASE [ExampleDatabase];

USE [ExampleDatabase];

DROP TABLE dbo.ExampleTable;

IsActive BIT,


How do you set SSIS package variable equal to an output parameter from a stored procedure that is ty


I have a stored procedure insertXXX that has one OUTPUT parameter of type numeric. The stored procedure looks like:



PROCEDURE [dbo].[insertIntoTable]
AS numeric OUTPUT




Stored procedure, with null items?


I build a stored procedure it should do something like this.
But the code is not ok.
I have some if statemants, i like to check if the var is filled. If its empty some checks het should not do.
But also the 'and'  is not ok. If i don't like the first 3 statemants, then the 4th  isnt running because you get: select * from artikelen where and (breedte >= ..........

So what is the best way to do a SP like this?
Is it possible to add an empty float to the stored procedure ?

USE [artinfo]
/****** Object:  StoredProcedure [dbo].[GetFilesByRole]    Script Date: 07/16/2008 16:43:36 ******/
ALTER PROCEDURE [dbo].[SearchArtikelen]
 @kleur nvarchar(50),
 @fabrikant nvarchar(50),
 @lengtevan float,
 @lengtetot float,
 @breedtevan float,
 @breedtetot float 

declare @str as nvarchar(5000)

set @str = 'SELECT *
FROM dbo.artikelen

if (@lengtevan is not null){
 set @str += '(Lengte >= ' + @lengtevan + ') '
if (@lengtetot is not null){
 set @str += 'and (Lengte <= ' + @lengtetot + ') '
if (@breedtevan is not null){

Variable names must be unique within a query batch or stored procedure. ???


Hi i am using sql data source to delete first from Child Table and second check if there is no child  go and delete the primary key in Parent table

this is my code

<asp:SqlDataSource ID="SDSOrdersGrid0" runat="server"
            ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
            DeleteCommand="Declare  @OrderIDinWeek
Set @OrderIDinWeek=(Select order_dtl_id from Weeks where IdWeek=@IDweek)
IF (SELECT count(*) FROM  weeks WHERE order_dtl_id =@OrderIDinWeek) =0
   delete from order_dtl where order_dtl_id= @OrderIDinWeek

pass datetime variable into execute sql task which has stored procedure


Hi all

I have a stored procedure something like this exec sp_name startdate, enddate. I have to run this in an ssis package.

The startdate and enddate values are loaded from a sql query, so i have declared variables (startdate,enddate) and got those values from the query using evaluateasexpression. Now, when i call the stored procedure in execute sql task it fails as the datatype from the variable is string and the stored procedure parameter (startdate,enddate) are datetime. Please help me.

The following are my settings in execute sql task editor

SQL statement : EXEC sp_name ?,?

parameter mapping

variablename: user::startdate

Direction: Input

data Type: Date

Parameter name : 0

Parameter size : -1


Passing a SSIS Variable with a string datatype to a Stored Procedure requiring a uniqueidentifier


I have a SSIS package that uses a SQL Server stored procedure as a data source. The parameter in the stored procedure is pulling data from a column with a uniqueidentifer (guid) data type. I created a package level variable in SSIS with a string datatype. SSIS variables do not offer a uniqueidentifier datatype.

I have the following text in the SQL Command text box:

exec dbo.spBillingReferenceSource
@BatchID = ? 

When I click the parameters button the mappings are @BatchID for the parameter (which is the exact name of the parameter in the stored procedure) and User::BatchID (the SSIS variable) 

When I run the stored procedure in preview mode in the SSIS dataflow, it returns an error message -- "No value given for one or more of the required parameters"

If I run the stored procedure by typing the value in the datasource box such as listed below, it works and delivers the proper records. I have even modified the stored procedure to convert the column to use varchar(36). However, it still returns the same error message. Does anyone have any suggestions on how I can get this to work. Thank you.


Inserting rows via stored procedure and under certain conditions


I'm using Dynamic Data with Entity Framework in VS2010.

Let's say my table has these fields:

PersonID (FK)
LocationID (FK)

Hypothetical scenario (it's easier for me to explain this way, so just bear with me for now)... But let's say each row in the table represents "how many items were sold by such-and-such employee at such-and-such location," where location and person are foreign keys which are referencing other tables.  Basically, there should be no more than a ONE row which has a particular combination of Person and Location.  Makes sense?

So, when inserting new rows using my Dynamic Data app, the insert form displays editable fields for Person (dropdown), Location (dropdown), and Items Sold (textbox).  How do I prevent users from inserting another row into the table containing an already-existing combination of Person and Location?   How do I displaying useful feedback to them in the event that they DO attempt to do this?

I have several thoughts about this, but since I'm new to Dynamic Data, I'm not sure which way to go.  For example:

Option 1:  Use "cascading dropdowns" approach in the insert form and only pull in the "allowed" combinations of the two dropd

Creating A Stored Procedure Which Searches Team Names



I'm have on my web page a text search box which I want users to type in there favourite football team and this will display a gridview of the teams with the replica shirts I offer.

This is where I thought about creating a stored procedure to carry out this task.

I looked online for ideas but I not found anything as yet.

If anyone done anything similar to my request please let me know.

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" />

Stored procedure generator?


Hi, I am looking for a stored procedure generator with full source code(C#) compatible with Visual studio 2010. I want to create my custom stored procedure code. Please send some link. Regards, ap.

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