I am developing ASP.NET 2.0 website. I need to know some about using stored procedure. I searched through google. But could now find a favourable repLy.
Here is ..
Which way is efficient, using SQL inside the code or as SRORED PROCEDRE, which one to use with ASP.NET?
Is the Stored procedure must be created withing the server or from my application?Can anyone please give some practicle explaination about this?
My advance thanks for all...
I want to retrieve data from SQL containing non English character but fail, can anyone shed me some light?
What I use currently:
Dim strSQL As String strSQL = "SELECT ArticleID, " strSQL &= "ISNULL(Body, '') AS Body, " strSQL &= "ISNULL(Subject, '') AS Subject " strSQL &= "FROM Articles " strSQL &= "WHERE (Subject LIKE N'%' + @Keyword + '%' OR [Body] LIKE N'%' + @Keyword + '%') " Dim con As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString")) Dim cmd As New SqlDataAdapter(strSQL, con) cmd.SelectCommand.Parameters.Add("@Keyword", SqlDbType.NVarChar).Value = keyword ...
I'm not so sure where should I place the letter "N", I use :
SELECT ArticleID, ISNULL(Body, '') AS Body, ISNULL(Subject, '') AS Subject, FROM Articles WHERE (Subject LIKE N'%SomeNonEnglishString%' OR [Body] LIKE N'%SomeNonEnglishString%')
in Query Analzyer, it works! But it failed in my program... oh my god...
I need to expand resursion level for resursive CTE expression within CREATE FUNCTION statement for inline table function to a value greater than default. It turns out that OPTION clause for MAXRECURSION hint perfectly works if I use it outside CREATE FUNCTION (as well as CREATE VIEW for non-parametrized queries), but it does not within CREATE FUNCTION statement - I'm getting error:
Msg 156, Level 15, State 1, Procedure ExpandedCTE, Line 34
Incorrect syntax near the keyword 'option'.
Here is the function:
create FUNCTION [dbo].[ExpandedCTE]
(
@p_id int
)
RETURNS TABLE
AS
RETURN
(
with tbl_cte (id, tbl_id, lvl)
as
(
select
id, tbl_id, 0 lvl
from
tbl
where
id = @p_id
union all
select
t.id, t.tbl_id, lvl + 1 from
tbl_cte inner join tbl t
on rnr.tbl_id = tbl_cte.id
)
select
id, tbl_id, lvl
from
tbl_cte
option (maxrecursion 0)
)
Please help!
Alexander.
P.S. I'm really sorry if it is about syntax, but I could not find it in the documentation.
Hi all, I want to learn about sp with examples,so can any one give me the best urls regarding this.and i want to write single sp for all like (insert,delete and update)in a single sp.Please guide me. Thank You
Hi I use the next StoredProcedure in Access, Inserts Users to tblUsers: INSERT INTO tblUsers ( UserName, Password, RetypePassword, Email, Comments ) VALUES (@UserName, @Password, @RetypePassword, @Email,@Comments);
I am New to Sql Server. Now i have to write two stored procedures.
Here are my requirements. If any one help please.
1)People often ask me to do this as well... change the name of the underwriter. For Mortgage Network underwriters (different than MGIC underwriters) all you need to do is:
Update mnetwork..unw_Nola set underwriter='<underwriters username>' where LoanID='<LoanID>'
So, I need you to write a stored procedure that will do exactly that. If you don't enter a loanID or username, the stored procedure should tell you that it can't complete the task and why. Also, the list of underwriters can be found in:
select LoginName from mnetwork..unw_LoginLookup where UnderwriterName = '<Name on the email>'
So for this one, you would select where underwriterName='John Brennan'.
For most usernames, it's just first initial last name (jbrennan in this case). You can make the stored procedure to both, if you want. If you enter an underwriter name, then it will translate to the loginname. If you enter the login name, it will just use that. You don't have to do all of that if you don't want. Just make sure the procedure verifies that the username is correct (in the table) and that is enough.
I want to execute something likeselect iif(type='credit',amount*-1,amount) from tablehow can i do that? help file says iif is used in MultidimensionalExpressions but that seems overly complicated for this task.any ideas?
I would like to know if there is any sort of improvement when using Inline SQL vs Stored Procedures as far as the execution plans are concerned when they are used in SSIS packages. I happened to create a SSIS package which calls Stored Procedures but internally they are using Linked Servers to get the data required. At this point they want to know what will be benefit that can be achieved when Inline SQL is used.
I would appreciate if anybody can give their thoughts or provide some informative articles like pros and cons.
I am using SQL Server 2005 now and I have a table with following columns. ID, FirstName, LastName, Email "ID" is the primary key (int) and is set auto generated (1 increment) I have a StoredProcedure to insert a new record. CREATE PROCEDURE Candidate_Create @FName nvarchar(255), @LName nvarchar(255), @Email nvarchar(255)ASINSERT INTO Candidate (FirstName, LastName, Email)VALUES (@FName, @LName, @Email)GO I want the ID to be returned as the same time when a new record is inserted, how can I do it ? Is it possible ?
Hi,I'm wodering if it's possible (and the correct syntax) to make a JOIN between a Table and a SP's result. This is my code, but it goes in error in the EXEC:1 SET ANSI_NULLS ON 2 GO 3 SET QUOTED_IDENTIFIER ON 4 GO 5 -- ============================================= 6 -- Author:Luca de Angelis 7 -- Create date: 22/08/2007 8 -- Description:Inserimento dei dati contabili nella tabella di log 9 -- ============================================= 10 CREATE PROCEDURE dbo.InsertIntoLog_dati_contabili 11 -- Add the parameters for the stored procedure here 12 @id_gestore tinyint 13 AS 14 SET NOCOUNT ON 15 BEGIN TRANSACTION 16 INSERT INTO CEL_log_dati_contabili(numero_telefonico, anno, mese, id_gestore, id_tipo_log) 17 SELECT CEL_traffico_temp.numero_telefonico 18 , CEL_traffico_temp.anno 19 , CEL_traffico_temp.mese 20 , @id_gestore as id_gestore 21 , 1 22 FROM CEL_traffico_temp 23 INNER JOIN 24 (EXEC dbo.CEL_SimConGestoreNoFilePeriodo @id_gestore, CEL_traffico_temp.anno + CEL_traffico_temp.mese) AS tabella 25 ON CEL_traffico_temp.numero_telefonico = tabella.numero_telefonico 26 27 INSERT INTO CEL_log_dati_contabili(numero_telefonico, anno, mese, id_gestore, id_tipo_log) 28 SELECT CEL_traffico_temp.numero_telefonico 29 , CEL_traffico_temp.anno 30 , CEL_traffico_temp.mese 31 , @id_gestore as id_gestore 32 , 2 33 FROM CEL_traffico_temp 34 INNER JOIN 35 (EXEC CEL_SimNelFileNoGestore @id_gestore) AS tabella 36 ON CEL_traffico_temp.numero_telefonico = tabella.numero_telefonico 37 38 INSERT INTO CEL_log_dati_contabili(numero_telefonico, anno, mese, id_gestore, id_tipo_log) 39 SELECT CEL_traffico_temp.numero_telefonico 40 , CEL_traffico_temp.anno 41 , CEL_traffico_temp.mese 42 , @id_gestore as id_gestore 43 , 3 44 FROM CEL_traffico_temp 45 INNER JOIN 46 EXEC CEL_SimNelFileNoUtente @id_gestore AS tabella 47 ON CEL_traffico_temp.numero_telefonico = tabella.numero_telefonico 48 49 IF @@error <> 0 50 BEGIN 51 ROLLBACK TRANSACTION 52 END 53 ELSE 54 BEGIN 55 COMMIT TRANSACTION 56 END Help me please...
Visual Studio 2008 Code VB I'm trying to create a stored procedure that will update a database table. I want to make sure that duplicate records are not inserted into the Database Table, so I used IF NOT EXISTS . With the below code I can update the table, however, you can not add additional rows to the table. Could someone tell me what is wrong, or how to fix it?
Thanks! losssoc ALTER PROCEDURE dbo.CaseDataInsert
AS BEGIN IF NOT EXISTS(SELECT ReportType,CreatedBy,OpenDate,Territory,Region,StoreNumber,StoreAddress,TiplineID,Status,CaseType,Offense FROM CaseData)INSERT CaseData(ReportType, CreatedBy,OpenDate,Territory,Region,StoreNumber,StoreAddress,TiplineID, Status,CaseType,Offense)VALUES(@ReportType,@CreatedBy,@OpenDate,@Territory,@Region,@StoreNumber,@StoreAddress,@TiplineID, @Status,@CaseType,@Offense)
To all, I looked at the MS-SQL pubs sample database and execute the example stored procedure reptq2 and I got 17 results set back. Where can I find an example using Visual Studio DataGrid or any means to get all these results from this SP.
AS BEGIN IF NOT EXISTS (SELECT * FROM CustomerDetails WHERE Name = @Name AND Number = @Number) BEGIN UPDATE CustomerDetails SET Number = @Number,Name = @Name,City=@City,SSN = @SSN where CustomerID = @CustomerID END ELSE BEGIN print 'CANNOT UPDATE' END END
This my storedproc. My problem is when i select customerID = 1 to update and if the same row having name = @name and number =@number Then the update should take place.
but if any other row other than CustomerID=1 having name=@name and number=@number Then the update is should not take place.
but The above stored procedure is not working like that.
so please some one help me with this. Thankyou Ramya.
For a reporting product I created a sp which created a temp table and then using my function below built. problem is the product won't allow me to create a temp table. With what I have below anyone have any creative ideas I could use. In-line sql, subquery views?
select enc_id,labcnt,order_name,date_due
reate FUNCTION fn_GET_ARRAY_VALUE( @DELIMITER VARCHAR(100), @STRING VARCHAR(1000), @ARRAY_POSITION INT) RETURNS VARCHAR(8000) AS BEGIN DECLARE @CURRENT_POSITION INT DECLARE @VALUE VARCHAR(8000) SET @CURRENT_POSITION = 0
WHILE @CURRENT_POSITION<@ARRAY_POSITION AND CHARINDEX(@DELIMITER,@STRING,0)>0 BEGIN SET @STRING = SUBSTRING(@STRING, CHARINDEX(@DELIMITER, @STRING, 0) +LEN(@DELIMITER), LEN(@STRING) -CHARINDEX(@DELIMITER, @STRING, 0) + LEN(@DELIMITER) ) SET @CURRENT_POSITION = @CURRENT_POSITION + 1 END
IF CHARINDEX(@DELIMITER,@STRING,0)=0 SET @VALUE = @STRING ELSE SET @VALUE = SUBSTRING(@STRING, 0, CHARINDEX(@DELIMITER, @STRING, 0) )
Hi, I'm trying to get MSSQL to choose which row to display from a result from a query, but it seems to need an 'IF' or something similar.
My query asks the database to pick out all rows that meet critera X, but sometimes (quite correctly) some of the rows created by query X are duplicates. (A data overlap nothing wrong with the query)
I want to be able to get the query to decide which one of these duplicates to display. There is a unique element.
In my example I want to remove ID 1 from the result, I can't use the 'active' column as this will remove ID3 fro the result. I want the query to recognise the duplicate based on the sysname, then choose to display the result with 0 in it.
Does anyone have a clue what I'm talking about? I'm loosing the plot.
Hi everyone!I tried to set my SqlDataSource's SelectCommandType to be a stored procedure. However the SqlDataSource failed to cache it. But if I just copy paste my stored procedure's content to my SqlDataSource's SelectCommand property, the cache just works. Is "StoredProcedure" as the "SelectCommandType" is not supported when caching the data? Or am I missing something here? Please help.
I am trying to write a function for some source to make a call out to and fill a RadioButtonList. I am running into a few problems though that I need assistance on. (I am new to DataSets) Here is the function to fill the RBL: 1 Private Function GetDataSet(ByVal QuestionID As Integer, ByVal QuestionType As Integer, ByVal LocaleID As Integer, ByVal GroupingNum As Integer) As DataSet 2 Dim cnn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString) 3 Dim cmd As New SqlCommand 4 cmd.CommandText = "usp_responses_sel" 5 cmd.CommandType = Data.CommandType.StoredProcedure 6 7 ' Fill usp_ with Parameters 8 cmd.Parameters.AddWithValue("QuestionID", QuestionID) 9 cmd.Parameters.AddWithValue("LocaleID", LocaleID) 10 cmd.Parameters.AddWithValue("GroupingNum", GroupingNum) 11 12 Dim da As New SqlDataAdapter 13 da.SelectCommand = cmd 14 Dim ds As New DataSet 15 da.Fill(ds, "response") 16 Return ds 17 End Function
So my issue is with line 15 [da.Fill(ds, "response")]. I pulled this function from somewhere else and am trying to tailor it to my needs. However, I do not understand what I need to do with this line and it keeps bombing out. I thought this references the DB Table but in my case, the SP has several tables joined together. Is this how I reference it from the calling source code? Please assist. Also, I am having problems understanding the binding process from the calling source. Here is my code that calls the function:1 Dim ds As DataSet = GetDataSet(CType(e.Item.DataItem("question_id").ToString, Integer), QuestionTypeID.Value, intLocale, 2) 2 rblResponses2.DataSource = ds 3 rblResponses2.DataBind()
What do I need to do with it from here and how can I work with it after it's bound? Thanks
hi i have a stored procedure witch Returns 0 or 1 dependig if exists some rows how can i acces that value in code behind? i tryied all, command.ExecuteScalar(), command.ExecuteNonQuery() but none works i need something like if(command.GetReurnValue) .... else .... thanks in advance
Can I know the best method to connect the sql server 2008 with Visual studio 2008, it will be helpful if I know how to access the stored procedures in the sqlserver via LINQ.
I need to execute stored procedure which is suppose to return GUID to my IF statement and if it is Nothing I execute other Stored procedures else some other procedures. My problem is that even though by looking at the data I know that after the execution of the procedure it should return some guid value it doesn't anybody who had the same issue??? That is the code block where I am trying to return guid from my stored procedure: getGroupID.Parameters("@GroupName").Value = dr.Item("Group ID").ToString() If getGroupID.ExecuteScalar() = Nothing Then 'Find Group by IP address if input Data Table doesn't have group getGroupIDByIP.Parameters("@IP").Value = dr.Item("IP").ToString() If getGroupIDByIP.ExecuteScalar() = Nothing Then insertGroup.Parameters("@GroupID").Value = Guid.NewGuid insertGroup.Parameters("@Group").Value = dr.Item("Group ID") insertGroup.Parameters("@ACCID").Value = getAccID.ExecuteScalar() insertGroup.ExecuteNonQuery() command.Parameters("@Group_ID").Value = getGroupID.ExecuteScalar() Else command.Parameters("@Group_ID").Value = getGroupIDByIP.ExecuteScalar() End If Else command.Parameters("@Group_ID").Value = getGroupID.ExecuteScalar() End If Thank you
Hello everyone,I am developing forums (Discussion Board) in C#.net 2005 with SqlServer. Right now i am having problem in fetching data from two tables.Here are the tables from which i want to fetch data.Topics Table Threads TableTopicID ThreadIDForumID TopicIDTopicName SubjectTopicDescription Replies UserID LastPostDatenow i want to fetch all the topic talbe data as well as total no of threads,Lastpostdate,UserID per topic.I am able to fetch topic table data and Total no of threads per topic through the following query.SELECT TopicID, ForumID, TopicName, TopicDescription,(SELECT COUNT(ThreadID) FROM Portal_Threads WHERE (TopicID = Portal_Topics.TopicID)) AS Threads FROM Portal_Topicsbut i am not able to fetch anoter two detail with subquery as i am getting error likeonly one expression can be specified in the select listorsubquery returned more than one value.can anyone tell me how can i fetch these two values per topic. should i use stored procedure and create temporary table and after fetching these values i can store it in temporary table and i can fetch values from that temporary table...please provide code snippet if possible as i've never used sqlserver before..Thanks in advance...Regards,Nil
I have a DTS package (AdIns) that inserts to an administrative table. The Administrative table utilizes the "with ignore_dup_key" option on the index. There are other admin jobs in the DTS that are based on the return code of a parent package.
The "3604:duplicate key ignored" is an expected result of the parent package, yet it sends an failure return code to the dependent (AdIns) package, causing erroneous entries to the final audit table.
How can I reset the return code from the parent package?
I need to create a StoredProcedure to calculate a field auto-inc. Who helps me? I tried many code, but i didn't have sucess.
My sample:
CREATE PROCEDURE SP_CT_ITEM AS
DECLARE @CONTADOR NUMERIC(008)
SET NOCOUNT ON
BEGIN TRAN
SELECT @CONTADOR = CAST(NM1_PARAMETRO AS NUMERIC(008)) FROM PARAMETRO (UPDLOCK) WHERE SIG_PARAMETRO = 'CT' AND GRU_PARAMETRO = '001' AND COD_PARAMETRO = 'ITEM'
UPDATE PARAMETRO SET NM1_PARAMETRO = @CONTADOR + 1 WHERE SIG_PARAMETRO = 'CT' AND GRU_PARAMETRO = '001' AND COD_PARAMETRO = 'ITEM'
I have to write a stored procedure.I have a search page having four fields.Giving any of the field should fetch the whole record and display in the gridview. My trouble starts here I have a button field in gridview1 . when i click on the button there should be another gridview which displays refunds of particular customer i.e from another table.There is only one common colum in the two tables. based on that colum value we have to fetch from second table. now my question is : how to capture the colum value of first select statement and give it as input to second select staement.
my code is here :
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go
ALTER PROCEDURE [dbo].[search] (@val1 varchar(225), @val2 varchar(50), @val3 varchar(50), @val4 varchar(50)) AS BEGIN Select*From customer where ((@val1 IS NULL) or (CNo = @val1)) AND ((@val2 IS NULL) or(LastName = @val2)) AND ((@val3 IS NULL) or(FirstName = @val3)) AND ((@val4 IS NULL) or(PhoneNumber = @val4)) Select * From refunds where CNo = @val1 END
here if i fill CNo text box in my search page its giving the value but all the time user may not give CNo.He may search customers based firstname ,lastname etc. so what should i do to capture CNo from first select statement and give it as input to second select statements
In a DataFlowTask with several OLE DB Destinations, how can I "last", before ending this DFT execute a storedProcedure?
This storedprocedure is used for saving metadata (taskname, rowcounts etc) regarding this DFT and I dont want to add an ExecuteSQLTask after the DFT in the Control Flow
I have to write a query for printing multiple barcodes, depending on the quantity of items that came in the store, based on the order number.
DECLARE @num INT SELECT BarCodes.BarCode, BarCodes.ArticleID, ArticlesTrafic.DocumentID, ArticlesTrafic.TrafficQuantity FROM BarCodes INNER JOIN Articles ON BarCodes.ArticleID = Articles.ArticleID INNER JOIN getAutoNumberTable(@num) ON @num=ArticlesTrafic.TrafficQuantity WHERE (ArticlesTrafic.DocumentID = @Param2)
The thing i would like to do, is somehow assign a value to @num and pass it to the getAutoNumberTable stored procedure, which generates a table of consequtive numbers, so that each record is displayed multiple times. Is it even possible to do it without using temp tables and loops?
i am not sure if this forum is right place to ask this question.. i am trying right a dos batch file to do setup of sql commands run by sqlcmd , run some dos commands etc
net start mssql$server sqlcmd -E .....
net stop mssql$server...
sqlcmd -E ....
in unix you can run isql with the sql commands place inside..
isql -Uuser -S server <<EOF select 1 select 2 go EOF
you can put above in a shell and it will run.
i am trying to do similar stuff in windows for sqlcmd.. how can i do it
only option i have is to create lot of .sql files and run with -i option on sqlcmd..
can some body let me know how to do a inline commands in dos? thx AK
