Tag: stored-procedures

Update query using inner join

Why I get the following syntax error in this section of stored procedure : Incorrect syntax near the keyword ‘FROM’. WITH CTE_Residence_Overtime AS ( SELECT * FROM #Residence ) UPDATE t1 SET t1.over_time = t1.over_time + CONVERT(TIME, CAST(CTE_Residence_Overtime.overtimeHours AS VARCHAR(2)) FROM r_overtime AS t1 INNER JOIN CTE_Residence_Overtime ON t1.[trans_date] = CTE_Residence_Overtime.[dayDate];

SQL – Execute stored procedure for all values in a table

I have a SQL stored procedure ‘A’ which validates certain bank account information for a given account and it accepts the account number as an argument ‘arg1’ I want to execute the procedure for all values present in Column X of another table XXX (all bank Accounts present in the Accounts table) I am not […]

Stored procedure returns different values in C# vs. SQL Server

I have a stored procedure (sql server 2012) with parameter @id int OUTPUT The procedure inserts to the database`s table some data and returns the ID of the affected row SET @id=SCOPE_IDENTITY() RETURN @id I test it from SQL Server and it works fine. But when I call it from C# : it indeed inserts […]

Query stored in NVARCHAR(MAX) does not fit

I have the following query: DECLARE @query AS NVARCHAR(MAX); SET @query =’ SELECT col1 [TÜR], col2 [KOD], col3 [BANKA/CARİ], col4 [BANKA HESABI], col5 [AÇIKLAMA], col6 [VADE], ‘+ @cols +’ FROM ( ( SELECT ”LEASİNG” [col1], d.REGNR [col2], cl.DEFINITION_ [col3], ”” [col4], d.DESCRIPTION [col5], c.PAYMENTDATE [col6], a.KDVLI- Isnull(b.KDVLI,0) [AMOUNT], c.TRCURR [TRCURR], e.CURCODE [CURCODE] FROM (SELECT LOGICALREF, […]

Stored Procedure, can you help me?

Below is my stored procedure. I want use stored procedure select all row of date from tbl_member and insert 2 table. But it’s not work. Some one can help me? Create PROCEDURE sp_test AS BEGIN SET NOCOUNT ON; Declare @A Varchar(255), @B Varchar(255), @C Varchar(255), @D int Declare Table_Cursor Cursor For select A, B, C […]

How put % in stored procedure for a variable @?

I have a stored procedure like this: ALTER PROCEDURE GetSoNumber (@SO varchar(255)) AS BEGIN SELECT OrderNo FROM Orders_Header WHERE (DVMOrderNumber LIKE ‘%’@SO & ‘%’) End How can I apply the SQL LIKE wildcard (%) on a variable (@SO) within a stored procedure? I tried with ‘%@SO%’, but doesn’t work. I want to select partial text […]

What should an INSERT stored procedure do with the Identity value?

I need to write a stored procedure to insert a row into a table. The table has an Identity column called ID. My question is should I return the value and if so, how? As far as I can see, I could return it as the return value, I could return it as an out […]

TSQL / SP INSERT statement with text & values

HI, I have an insert statement in a Stored Procedure that requires the value data to be a combination of text and variables/inputs. However my SQL Manager is giving an error on this ‘camp’ value when trying to compile it. Here is the statement as it currently exists; BEGIN TRAN INSERT INTO TBL_DONATIONS ([donations_MemberID], [donation_Ref], […]

T-SQL Cursor in stored procedure

I am using a stored procedure and I want to use cursor for inserting new data (if data exist I want to update) ALTER Procedure [dbo].[conn] @ResellerID int, @GWResellerID int, @UserName varchar(50), @Password varchar(50), @URL varchar(100), @ServiceType int, @ServiceDesc varchar(50), @FeedFrom bit, @PublicKey varchar(max) AS declare gateway cursor for select * from reseller_profiles where main_reseller_ID […]

Default null values in stored procedures are not Null

I have been testing a faulty procedure, and I realised that the problem is Null values being passed in are not being defaulted to the procedure value… Create Procedure TestVarcharMaxIsNull (@myVar varchar(MAX) = ”) as Select ‘Hello’ Where @myVar Is Null Go Exec TestVarcharMaxIsNull Null Go Exec TestVarcharMaxIsNull ” Go Exec TestVarcharMaxIsNull Go Drop Procedure […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.