.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

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

Posted By:      Posted Date: December 04, 2010    Points: 0   Category :ASP.Net

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

View Complete Post

More Related Resource Links

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.

selecting between query and stored procedure

<!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} /* List Definitions */ @list l0 {mso-list-id:1033506322; mso-list-type:hybrid; mso-list-template-ids:369124590 67698703 67698713 67698715 67698703 67698713 67698715 67698703 67698713 67698715;} @list l0:level1 {mso-level-tab-stop:.5in; mso-level-number-position:left; text-indent:-.25in;} ol {margin-bottom:0in;} ul {margin-bottom:0in;} --> What would prefer to pull data out from sources stored procedure or query? and why?


Hi Everone,    Can any one help me out in creating a dynamic select statement, so far my SP look like belowALTER PROCEDURE [dbo].[CheckAccess]@TABLENAME VARCHAR(100),@JNID INT=0ASBEGIN    SET NOCOUNT ON;    DECLARE @SQ VARCHAR(500)    DECLARE @ID INT =0     SET @SQ= ' SET @ID = SELECT TOP 1 ID FROM ' +  @TABLENAME + ' WHERE PID ='+ @JNID       PRINT @SQ    EXEC(@SQ)       IF (@ID>0)      BEGIN        RETURN 1      END    ELSE      BEGIN        RETURN 0      ENDENDGOlet me know where i am going wrong

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.

Exception when applying a policy against stored procedure names...

Hi There,       I have recently created a policy that checks stored procedure names and prevents any to be created and/or changed to anything that is not prefixed with 'spr_'. I have a simple Cindition called SPNameCondition who's expression is @Name LIKE 'spr_%' I also have my Policy called SPNamePolicy who's condition is set to SPNameCondition. It is Enabled and targets every stored procedure in every database. I have the evaluation mode set to on change: prevent and no server restrictions. After creating my policy, I checked it by evaluating it against all existing stored procedures in our databases. This worked as I expected as it flagged up all existing stored procedures names that didn't start with 'spr_' as in error. The problem I am having though, is when I create a new stored procedure in SSMS and execute I get the following exception: A .NET Framework error occurred during execution of user-defined routine or aggregate "sp_execute_policy": System.TypeInitializationException: The type initializer for 'Microsoft.SqlServer.Management.Dmf.PolicyEvaluationHelper' threw an exception. ---> System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.SqlServer.Diagnostics.STrace, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file spe

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?

Making Batch Predictions from a stored procedure

I have a decision tree that will make predictions about customers as they make their first purchase with our company. I'd like to have a table append the predictions and the probabilities of these predictions to a customer table in our reporting database.  So from a table that has all my inputs how can I make a stored procedure that would append my prediction and the probability without having to call an open query statement for each row?   In other words how can I make a batch prediction on Table with a stored procedure? Select t.[Key], Predict([Predict_Tree].[Predict Variable]) as [Prediction], predictProbability([Predict_Tree].[Predict Variable]) as [Probability] From [Predict_Tree] Natural Prediction Join (Select 12280 as [Key], 999 as [Input1], 'Stuff' as [Input2], 'MoreStuffl' as [Input3], 'LotsOfStuff' as [Input4], 'Deleted' as [Input5], 'For' as [Input6], 'Privacy' as [Input7], 1 as [Input8], 2 as [Input9], 'Purposes' as [Input10], 3 as [Input11], 1 as [Input12], 0 as [Input13], 'bTard' as [Input14]) AS t

Its posible to do a select without the table and the column names into a stored procedure?



I need your help. Imagine that I have two tables:

  1. ALUMN wich has three columns: IdAlumn, Name and Address.
  2. SPORT wich has columns: IdSport, Name, Players and Description.

Supose that my tables could be differnced by a number 1 is for ALUMN and 2 is for SPORT.

And the columns of each table too. So 1 is for IdAlumn, 2 is for Name and 3 is for Address. 1 is for IdSport, 2 is for Name 3 is for Players and 4 is for Description.

For example the combination of table 2 and column 3 is SPORT-Players.

Then I want to do an stored procedure that receives two parameters (the table, the column) @tab and&

Stored Procedure takes longer than the query.



I'm stuck up in a situation where my sp takes longer time but the query takes lesser time. I went through all the posts mentioned in the T-sql Forum and i tried all the possible resolutions mentioned there but it did not help me.

I have a sp which has around 20 parameters and i tried assigning them to local variables as mentioned in various posts but that did not help me.

Could you please help me out with this problem?







Send NULL value to a stored procedure variable


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

Performance of a Stored procedure : Optimum number of rows to be handled in a batch


In a stored procedure is there anything called as optimum number of rows that need to be handled in a batch

as In, If there is a SP,

   that picks up data(via union operation) from various objects and puts them into a temp table after which the required data is moved to various tables,The above scenario is handled as a batch run where in 1 million rows are processed in a batch and the next 1 million rows in a next batch,so on and so forth.

   Is there any optimum number of rows that can be processed in the above case?

  When we increase rows processed per batch we have seen improvements in performance of the SP,but not sure when it will start deteriorating.




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?

How to create a batch file using a stored procedure



Please let me know how to create a batch file with some content in it, using a stored procedure in SQL 2000.

Thanks in advance.



Stored Procedure Column Names - A Suggestion


When a data source calls a stored procedure, you often don't see the column names exposed for mapping in the dataflow designer. There are a number of work-arounds involving SET FMTONLY OFF or setting up a dummy select statement early in the stored procedure, non of which are entirely satisfactory.

Perhaps the SSIS developers could learn something from SSRS here, after the data from a datasource is previewed, the column names are available in the Report Desginer no matter how complex the stored procedure might be.

I would have thought that when you preview the data from the strored procedure in BIDs, that all the required information about the column names and data types returned by the stored procedure could be captured and made available in the data flow designer.

Can we create Stored Procedure from SSAS MDX Query


HI All,

Is this possible to create STORED PROCEDURE TO FROM MDX QUERY,if yes please tell me how and also can we use that STORE PROCESDURE in SSRS report.



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




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