Tag: stored-procedures

saving a result from a tsql function in a stored procedure

I am having problems with a stored procedure that I am writing for SQL Server. I am calling a function within the stored procedure which returns a 2 column table with a value. This value I want to assign to a VARCHAR variable to be used throught the rest of my store procedure. DECLARE @TEAM […]

SQL Cursor w/Stored Procedure versus Query with UDF

I’m trying to optimize a stored procedure I’m maintaining, and am wondering if anyone can clue me in to the performance benefits/penalities of the options below. For my solution, I basically need to run a conversion program on an image stored in an IMAGE column in a table. The conversion process lives in an external […]

OPENJSON stored procedure

I have this data passed from my repo [1,2,3,4,5], it is a List<int> converted to a json string. Now I want to insert it into the database. How should I write my query? INSERT INTO CommitteeMember SELECT @committeeID, * // how to call it? FROM OPENJSON(@membersJson) AS json

Largest text usable variable in MSSQL 2005 Stored Procedure?

What is the largest size text usable variable in a MSSQL Stored Procedure. I’m seeing that largest size you can use is a varchar(8000). We can use ntext, text, etc. So does it require stitching varchars together? What about if I used a CLR Stored Procedure??

SQL Server Stored Procedure – INSERT command – DateTime error sequence

Procedure is sync, ok? But, how explain that. I have a table with the fields (not specific like that): ID INT PRIMARY KEY IDENTITY, DATE DATETIME NOT NULL The insert executed by SP is: (This SP is call inside another one) INSERT TABLE VALUES ( SYSDATETIME() ) BUT, the table values are: ID | DATETIME […]

SQL Server – How to select record where my value in split column

This is result that I want: I wrote a stored procedure: ALTER PROCEDURE [dbo].[test_Split] @item INT AS CREATE TABLE #temp ( Id INT, Name NVARCHAR(50), List NVARCHAR(100) ) INSERT INTO #temp VALUES (1, ‘John’, ‘1,2,4,6,7’), (2, ‘Fox’, ‘7,23,42,10,17’), (3, ‘Rose’, ‘51,72,14,16,37’), (4, ‘Alex’, ‘1,22,84,56,47’) SELECT * FROM #temp When I execute test_Split, I get the […]

Entity Framework does not catch SQL Exception

I am using stored procedures and running the stored procedures using “FromSql” feature provided by Microsoft Entity framework Core. When there is a SQL exception Entity Framework does not catch the exception at all. For example below, the stored procedure “GetAppSections” is missing in SQL.When I run the app In the debug mode I can […]

SQL Server – How would I rank products from the last month?

I’ve already started to flesh things out a bit, but I’m having trouble adding in some sort of window function to get the intended result of having the data, products, called in my code ranked by sales in the last month. If any help could be given, I would greatly appreciate it! Here is what […]

Exporting/Importing Stored Procedures between DBs

I have two SQL Server 2005 databases, one is for development and the other is on the final production server. I would like to know the fastest way of ensuring that the production database has the exact same stored procedures (number and most recent version). Assumptions: Databases have same table schema. Production database is currently […]

get the value of variable name stored in SQL Sproc

This is what I am trying to do Declare @Var varchar(50) Declare @Num1 varchar(50) Declare @Num2 varchar(50) Declare @Counter smallint Set @Counter=1 Set @Num1=’Hello’ Set @Num2=’Hi’ while (@Counter<2) begin Set @Var=N’@Num’+convert(varchar,@Counter) //Now I want to get the value of ‘@Num1’ that is stored in @Var when @Counter=1 //Help Needed Set @Counter=@Counter+1 end

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