Tag: stored-procedures

Joining 3 tables and removing nulls from 2nd and 3rd table in SQL Server

I would like to join 3 tables and remove null in second and third tables where records doesn’t match due to join. I kind of got the result but not able to remove null and hence why when I take this query into SSRS I am losing a record. Here are my 3 tables and […]

Passing DateTime parameter to stored procedure using EF(DB first)

I created a stored procedure in SQL Server 2012 ALTER proc [dbo].[select_alltypes] @cdin_startunstufdate1 DateTime = null, @cdin_startunstufdate2 DateTime = null AS BEGIN SET NOCOUNT ON; SELECT 0 as shipid, i.cdin_cdindexid, p.pinv_PerformaInvID, coalesce(i.cdin_serial, 0) as depno, coalesce(convert(datetime, left(convert(nvarchar, i.cdin_startunstufdate, 120), 10), 120),’-‘) as deidate, coalesce(i.cdin_goodsDesc, ‘-‘) as gooddesc, coalesce(i.cdin_Customdeclar, ‘-‘) as custdec, coalesce(i.cdin_NoofPackages, 0) as pkg, […]

Passing irregular xml file to the stored procedure

I have a sample xml as follows. I am trying to get all or specific data and then insert into the my sql table which has the same columns representing the values coming from xml. I looked through some solutions but the xml files are not formatted like I have in here. Can you help […]

Split String Value in SQL for Flexible Filtering

I have a function where in user can assign multiple categories (food, non food etc) to a certain Tenant. See sample Data Table Table: tblSales date tenant sales category 1/1/2015 tenant1 1000 Food,Non-Food,Kiosk 1/1/2015 tenant2 2000 Food 1/1/2015 tenant3 1000 Non-Food,Kiosk The system should be able to load record when the user selected any of […]

Stored Procedure result into temp table in Azure Data Warehouse

In Azure Data Warehouse, i have a Stored Procedure which will return the result of SELECT command. How to push the Stored Procedure result into temp table? I tried the below query and it is returning an error message. CREATE TABLE #temp (name varchar(255), created_date datetime) GO INSERT INTO #temp EXEC sp_testproc Output message: Msg […]

SQL Server stored procedure: setting default value for input parameter though query execution

Suppose I have a following stored procedure: CREATE PROCEDURE test @my_var varbinary AS SELECT @my_var as my_var However I want to modify it so that @my_var has a default value. I do not know what that value is literally, but I know where to get it in the database. Something like: declare @var int SELECT […]

Our SQL Server stored procedure “dies” – but how can I find the cause?

We have a SQL Server Stored Procedure which does a very long, painful INSERT INTO.. SELECT FROM… command. It then writes a status message, via a second stored procedure, to say if it was successful or not. BEGIN TRY EXEC [Add_To_Log_Table] ‘Starting the INSERT command…’ INSERT INTO [Very_Large_Table] SELECT /* About 30 fields */ FROM […]

Query with variable tablename

I don’t know why I keep bumping into these, but I have a small problem with a stored procedure. The only special in it is the variable Tablename: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE dbo.ProdTijdCompare @TABLENAME SYSNAME, @scanner nvarchar(50) AS BEGIN SET NOCOUNT ON; DECLARE @SQL NVARCHAR(MAX); SELECT @SQL = ‘select […]

How to get a single column's value from a stored procedure call within a stored procedure?

I have the following procedure: SP_Validate_User_Main ( @UserName VARCHAR(20), @Password VARCHAR(20), @From VARCHAR(10), @IP VARCHAR(15) ) — does a bunch of stuff, then the following line is the last to execute EXECUTE SP_Validate_User @aUserName = @UserName, @aPassword = @Password Depending on the validity of the data passed to SP_Validate_User, 1 of 2 statements will then […]

Pass a Variable to SQL Stored Procedure ByRef?

Is it possible to assign a value to a variable, pass it to a stored proc, and then within the stored proc: 1.) use the value passed in, 2.) change the value assigned to the variable, and 3.) pass the variable back out? I am attempting to setup a “Time Hack” that I can intersperse […]

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