Dynamic sql inside stored Procedure-sql server 2008

The below when i tried to execute throw an error Msg 208, Level 16, State 0, Procedure CallView, Line 11 Invalid object name ‘#table’. CREATE PROCEDURE [dbo].[CallView] AS SET NOCOUNT ON; declare @name as varchar (max) select @name = VIEW_NAME from ZBT_MAPPING_TABLE MT INNER JOIN OUTPUT_DL DL ON MT.COMPID = DL.COMPID — insert to temp […]

How to set variable value from column data?

I want to declare a variable for reusability for this query: SELECT SUBSTRING(Email, CHARINDEX(‘@’, Email) + 1, LEN(Email) – CHARINDEX(‘@’, Email)), COUNT(*) FROM Student GROUP BY SUBSTRING(Email, CHARINDEX(‘@’, Email) + 1, LEN(Email) – CHARINDEX(‘@’, Email)) The variable holds SUBSTRING(Email,CHARINDEX(‘@’, Email)+1,LEN(Email)-CHARINDEX(‘@’, Email)) But it seems that the column data is not reachable outside SELECT statement: DECLARE […]

SQL Server : update column from table if one of the record >= X

I need help in updating one of my columns in an existing temp table everytime one of the metric is >= 4. Below is my query that I am working on: DECLARE @Met NVARCHAR(MAX) SET @Met = ‘MET’ DECLARE @NotMet NVARCHAR(MAX) SET @NotMet = ‘NOT MET’ UPDATE #TmpTbl SET Score_Oct = (CASE WHEN ([Type] LIKE […]

Rounding the value to the nearest 50

I am trying to round down the value to the nearest 50. 1-50 it should round down to below 00 and when its 51-rest then it should round down to 50 ex: 245 (until 1-49) its should round down to 200 258 (from 50-99)then it should round down to 250 I tried this,its wrking good […]

Can't use BETWEEN for SQL Server dates

I’m having an excruciating time with yyyymmdd dates. In this DB, null dates are stored as empty string. This executes just fine and I can manually check there are no badness in the dates SELECT effective, expiration FROM (SELECT CONVERT(date, date_effective, 112) as effective, CONVERT(date, date_expiration, 112) as expiration FROM … ) AS X But […]

Add values of a column and display added result in new table

The table with the data that I have In the above table I have the columns : weekNumber , weeklyHours , points_Rewarded. There are four employees : a,b,c,d I have the values for week1,week2,week3, and so on ( I can have data for many more weeks also such as week4,week5, etc) I want to write […]

Using Full-Text indexing to crawl binary blobs

If i store binary files (e.g. doc, html, xml, xps, docx, pdf) inside a varbinary(max) column in SQL Server, how can i use Full-Text indexing to crawl the binary files? Imagine i create a table to store binary files: CREATE TABLE Documents ( DocumentID int IDENTITY, Filename nvarchar(32000), Data varbinary(max), ) How can i leverage […]

WSO2 API Manager authentication Violation of UNIQUE KEY constraint error

We have implemented WSO2 API Manager (v1.10.0) in a distributed architecture as outlined in the online documentation here. This consists of the following (on 5 separate servers): Gateway (x2) Publisher & Store (on a single server) Key Manager (x2) These are wired-up to the 3 normal API Manager databases (Registry, User Manager & API Manager), […]

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 […]

How to EXECUTE dynamic query in WITH clause in SQL Server

Here is the my sample query, Can any one suggest me how to EXECUTE dynamic query in WITH clause. DECLARE @V_TBLID NVARCHAR(MAX) = 1, @V_QUERY NVARCHAR(MAX) = ”, @IN_PAGESIZE INT = 5, @IN_PAGEINDEX INT = 1; SET @V_QUERY = N’SELECT * FROM MYTABLE’+ @V_TBLID; WITH RESULTS AS ( EXEC(@V_QUERY) ) SELECT * FROM RESULTS WHERE […]

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