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


Post New Web Links

sproc calling sproc - output value problems

Posted By:      Posted Date: October 14, 2010    Points: 0   Category :ASP.Net
 

I have the following two sprocs:

USE [Ecotech]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Ecotech_AddUser]
@aspnet_id uniqueidentifier,
     @last_name nvarchar(255),
     @first_name nvarchar(255),
     @address1 nvarchar(255),
     @address2 nvarchar(255), 
     @zip nvarchar(10),
     @city nvarchar(255),
     @tlf_mobile nvarchar(16),
     @tlf_home nvarchar(16),
     @tlf_office nvarchar(16),
     @country nvarchar(16),
     @email nvarchar(50),
     @status int output
AS
BEGIN
    SET NOCOUNT ON;

    if (select COUNT(*) from ecotech_users where aspnet_id = @aspnet_id) > 0
        begin
            set @status = 0
        end
    else
        begin
            insert 
            into ecotech_users 
                (aspnet_id, 
                 last_name, 
                 first_name, 
                 address1, 
                 address2, 
                 zip, 
                 city, 
                 tlf_mobile, 
                 tlf_home, 
                 tlf_office, 
                 country,
                 email)
            values 
                (@aspnet_id,
                 @last_name,
                 @first_name,
                 @address1,
                 @address2, 
                 @zip,
                 @city,
                 @tlf_mobile,
                 @tlf_home,
             


View Complete Post


More Related Resource Links

DATE Output Parameters for SPROC

  

Hello everyone,


I have a question.

How do we define date output parameters for a stored procedure?

before you answer,I should mention that I am not referring to the sql syntax definition ,which is @mydate datetime output.

This is ok,what i am asking is HOW DO I SPECIFY IT IN LINQ WHEN I NEED TO CALL THE STORED PROCEDURE IN THE SERVER SIDE CODE.

I am repeating that I do I am not referring to the sql definition .

I would appreciate your help.

Thank you


How to avoid network problems while calling a webservice in a SQL CLR trigger?

  
Hi All, My Goal: DB synchronization between SQL server 2005 and Mysql database via web services. I have created a SQLCLR trigger, in which i'm calling a web service to sync/update remote (MYSQL) DB over a specific constraints. After getting the acknowledgment from web service i'm updating the sync flag to success (In SQL SERVER2005). If any network delay happens i am unable to know whether sync has done successfully or not. How to avoid network dependency here? Is there any reliable queuing mechanism available in sql server 2005 to eliminate dependency over network? I am looking any suitable service/approach in sql server 2005 that can take care of calling web services as asynchronously and update the status of sync flag irrespective of network. Thanks in advance if anyone provide the good approach as step by step in detail.

Sproc with dynamic Where fields

  
I've got a textbox where the user enters data and I've got a DropDownlist, with 2 fields in it (code and tag) I need to create a stored procedure which uses the textbox, but it also uses the field names from the DropDownList it's kind of  like this ' if user chooses 'code':where code= textbox1.text 'if user chooses 'tag':where tag=textbox1.text How can this be done?

Dynamic Sproc or Multiple Sprocs

  
Hi All, I am currently working on a system where a section of the application (Tasks) uses one Sproc and Consistently built views to generate the Tasks. The idea being that if you use a consistent build for your views and add them to a Lookup table then the Procedure can iterate through the task views and generate the Tasks for a given person. My question is around this process. In Oracle I probably would have built a package and then added separate procedures for each of the new views instead of dynamically generating the list. I really like that this process enforces consistency, but something about it just doesn't feel right (Lookup table and iterating through the views to generate output). I just wanted to see what the SQL gurus out there thought about this process. I am not pushing a right or wrong just trying to always question the way I am doing things and look for a better method. Always appreciate the feedback.   --B

Using sproc as data source (newbie).

  
I've succesfully displayed data from my legacy database using a sproc to implement all the business rules behind the query. Some of the data, though, is based on subqueries and is not in the base table displayed in the default list of tables. For example, order totals, address logic, etc. When I go to edit or display details from a selected record, I receive Invalid Column name errors on the generated fields. Any quick solutions or pointers to examples on how to handle this scenario? Thanks. 

SET statement in sproc not working

  

ALTER PROCEDURE [dbo].[EditTable]
@passwordHash nvarchar(50),
AS
UPDATE Table
SET passwordHash = CASE WHEN @passwordHash = NULL THEN passwordHash ELSE @passwordHash END

I want to keep the old password if the user leaves the password field blank, otherwise store the new password.

I have also tried "IS NOT NULL" instead of "= NULL", but without success. I've tried to send in an empty string and a DBNull value but it never keeps the old password.

Anyone?


Best way to build a query /sproc to support filtering

  

Hi All,

 

I have a scenario where I will be getting search results from a front end UI.  If nothing is passed I should return all results, but if the users have selected one to many choices I will need to filter based on those choices.  My delima is how to handle this approach.  The data will be coming in the form of XML and I will need to shred it to get the criteria.

I was thinking I could use "WHERE EXISTS" Clauses and if there are any results passed to me I could fill a table variable with everything and join to it.....if they did make selections then I could fill the table with just those choices. 

I really just don't like the idea of having to fill the table with everythign when nothing is selected. 

Just wondering what others have done to handle this type of scenario?

Thanks !

--B


Getting data from sproc with two selects

  

Hi,

Anyone know how to do this? I was thinking about making a sproc with something like

select * from users where @group not in member_of

select * from users where @group in member_of


Since I'm dead tired, that's not supposed to be a real query...just an approximation ;)

Cheers!


Return a user and a set of values from the same sproc

  

I have a user table with userID, userName ...the standards.
I also have a "Material" table with materialID, materialName, etc.

I combine these in another table "UsersMaterial" with userID and materialID, depending on which materials the user has chosen (between 0 and 5).

Now I need a stored procedure that returns the user information + the set of materials that is mapped to the user.

But I can't see how I can return one row of user information combined with a set of the user materials? Do I join or can I return a set + one row?


Thanks in advance!

Niklas


why is SPROC inserting two records instead of one?

  

Why am I getting two records inserted into the table, when I'm expecting and needing one?  The VB code below is checking whether the HTTP is secure or not, and if it is then execute the SPROC.  But for whatever reason, I'm getting two records inserted about 5 seconds apart.  Does anyone know why and how to fix?  I just need one record.  thanks

 

SPROC =======================================================================

ALTER PROCEDURE InsertVisitorData 
	-- Add the parameters for the stored procedure here
	@IPAddress varchar(40),  --IPv4 or IPv6
	@Browser varchar (150)	
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	INSERT INTO Visitors (ip, browser, time)
		VALUES (@IPAddress, @Browser, GetDate())
END;

VB ==========================================================================

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If HttpContext.Current.Request.IsSecureConnection.Equals(True) Then

            Dim ipAddress As String = Request.ServerVariables("REMOTE_ADDR")
            Dim browser As String
            Dim BCaps As HttpBrowserCapabilities
        

views in sproc

  

Hiii Coders,

I m writing a sproc and trying to create a view inside it but got an error:

Here is my code-


create proc proc2
@cust_id varchar(30)
as
begin

create view cust_info
as
select customer_no,customer_name,bill_amt
from customer_table
where
customer_no=@cust_id
end


error: Incorrect syntax near the keyword 'view'.

why i m getting this error?

plz rep asap

thanx nad regards




Selecting from table where value is in results from a sproc

  

I'm stuck again! I cannot get the following to work - hopefully it will be self-explanatory on what I am trying to achieve. Any suggestions please? Smile

select * from TABLE where [Value] in (execute dbo.spProcedure 'param')



problems with trace.writeline not writing output messages on multiple instance/thread service

  

Hi,

I've come across something strange and can't quite figure out what could be going wrong. I have a windows service which processes queue items in a threadpool (default 5 threads).

So, during testing, i "debug, start new instance" and spin up about 4-5 instances of the service, and on average there is about 25 threads going at same time.

My trace code:
Trace.WriteLineIf(traceOutput.TraceVerbose, string.Format("processing Item for {0}", item.id));

 

this seems to work for a while and writes the trace code to the output window, but after a while, the trace output just stops being output to the window.. has anyone else experienced this before or could know what might be going wrong?

 

thanks,

Paul


Top .NET Performance Problems and how to avoid them

  
Every time I work with one of our .NET customers to help them with managing their application performance I come across the same problems as seen with other clients before: lots of ADO.NET queries, many hidden exceptions in core or 3rd party .NET libraries, slow 3rd party components, inefficient custom code

Comma-Delimited Output

  
One of the common tasks performed when retrieving data from a SQL Server database is returning the result as a comma-delimited output instead of a result set. This task can be performed by using a CURSOR selecting the column to be concatenated together. Each row returned by the CURSOR is then concatenated together into a variable separating each one by a comma.

Enterprize library 4.1 getting output parameter after adding record in db, plz guide

  

Hi,

I need to get output parameter (flag) from db after saving record in database. I am using Microsoft Enterprise Library 4.1 for DB. I am inserting record using I Data reader.

Please guide

Thanks


Visual Studio 2008 Memory problems

  

My memory in task manager reaches about 900,000K  and I don't know why it does this. Definately slows everything down, especially when I rebuild my tableAdapters, takes about 30 seconds sometimes to rebuild the project.


Categories: 
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