Dynamic SQL && Output Params
Feb 7, 2005
I have a long running trigger that makes calls to several tables in order to get values for a list of parameters before doing my final INSERT statement into a different table.
One of my parameters is for a local language translation of a particular word which is stored in a table. The problem is - I do not know the name of the table until runtime when I dynamically build the name as follows:
DECLARE @SQL nVarChar(200)
SET @SQL = 'SELECT @Translation = nvcLocalEventDescription FROM Monitoring.dbo.tblSignalTemplate'
+ CAST(@MonitoringCentreID AS nVarChar) + ' WHERE nvcEventDescription = "' + @EventDescription + '"'
EXECUTE Management.dbo.usp_parmsel_LocalEventDescription @SQL, @LocalEventDescription OUTPUT
If there is a MonitoringCentreID of 1234, then there will be a table named tblSignalTemplate1234 - which will contain a nvcLocalEventDescription field containing the value that I am after. Here is the code for the stored procedure...
CREATE PROCEDURE [dbo].[usp_parmsel_LocalEventDescription]
@strSQL nVarchar(150),
@Translation nVarChar(100) OUTPUT
AS
EXECUTE sp_executesql @strSQL
GO
The error I get is "Must declare the variable '@Translation'" - which has thrown me a little as it declared on the 3rd line of the stored proc.
Anyone got any ideas where I am going wrong, or as usual, is there a simpler way ?
Steve.
View 2 Replies
ADVERTISEMENT
May 26, 2004
Hi,
I've got the following C# function to add a customer record to the database. The record gets added without any problems but the OUTPUT PARAMETER (Parameter[10]) is always NULL and I can't see why. I'm also using the Microsoft Data Application Block.
Here's the C# function:
public void SaveCustomer(int customerId,string customerName,string address1,
string address2,
string town,
string county,
string postcode,
string webSiteAddress,
string mainTelNo,
string mainFaxNo)
{
try
{
SqlParameter[] parameters = DA.SqlHelperParameterCache.GetSpParameterSet(this.ConnectionString,"UpdateCustomer");
parameters[0].Value = customerId;
parameters[1].Value = customerName;
parameters[2].Value = address1;
parameters[3].Value = address2;
parameters[4].Value = town;
parameters[5].Value = county;
parameters[6].Value = postcode;
parameters[7].Value = webSiteAddress;
parameters[8].Value = mainTelNo;
parameters[9].Value = mainFaxNo;
parameters[10].Direction = ParameterDirection.Output;
int RetVal = DA.SqlHelper.ExecuteNonQuery(this.Connection,"UpdateCustomer",parameters);
if (RetVal > 0)
{
int Key = Convert.ToInt32(parameters[10].Value.ToString());
}
}
catch (Exception ex)
{
throw new Exception(ex.Message.ToString(), ex);
}
}
And here's the SQL Server 2000 Stored Procedure:
CREATE PROCEDURE UpdateCustomer
@CustomerId int,
@CustomerName varchar(100),
@Address1 varchar(50),
@Address2 varchar(50),
@Town varchar(30),
@County varchar(30),
@PostCode varchar(10),
@WebSite varchar(50),
@MainTelNo varchar(15),
@MainFaxNo varchar(15),
@ReturnValue int OUTPUT
AS
IF @CustomerId = 0
BEGIN
INSERT INTO Customer
(CustomerName, Address1, Address2, Town, County, Postcode, WebSite, MainTelNo, MainFaxNo)
VALUES(@CustomerName, @Address1, @Address2, @Town, @County, @Postcode, @WebSite, @MainTelNo, @MainFaxNo)
IF @@ERROR = 0
BEGIN
SELECT @ReturnValue = @@IDENTITY
END
ELSE
BEGIN
SELECT @ReturnValue = -1
END
END
ELSE
BEGIN
UPDATE Customer
SETCustomerName = @CustomerName,
Address1 = @Address1,
Address2 = @Address2,
Town = @Town,
County = @County,
Postcode = @Postcode,
WebSite = @WebSite,
MainTelNo = @MainTelNo,
MainFaxNo = @MainFaxNo
WHERECustomerId = @CustomerId
IF @@ERROR = 0
BEGIN
SELECT @ReturnValue = @CustomerId
END
ELSE
BEGIN
SELECT @ReturnValue = -1
END
END
Any help would be appreciated as it's starting to drive me mad now!!
View 7 Replies
View Related
May 15, 2005
I have a stored procedure that I use to return Purchase Orders from our PO system. It returns the data rows for PO's that match the criteria passed in (including the page to show etc.) + it returns two output params, Number of rows and Number of Pages.
Using query analyzer I can confirm the query works exactly as we want. I cannot however seem to get the data out to our ASP.net app.
Here is a function that I use in one of my classes:
<code>
Function fnListPOsByCoordinatorIDPaged(ByVal strCoordinatorID As String, ByVal intPOStatusID As Int16, _
ByVal intUserTypeID As Int16, ByVal intArchived As Int16, _ByVal intPageNum As Int32, ByVal intPerPage As Int32, _ByVal strConn As String) As SqlClient.SqlDataReader
Dim dr As SqlClient.SqlDataReader
SqlConnection1.ConnectionString = strConn
prListPOByCoordinatorPaged.Parameters("@CoordinatorID").Value = strCoordinatorIDprListPOByCoordinatorPaged.Parameters("@POStatusID").Value = intPOStatusIDprListPOByCoordinatorPaged.Parameters("@UserTypeID").Value = intUserTypeIDprListPOByCoordinatorPaged.Parameters("@Archived").Value = intArchivedprListPOByCoordinatorPaged.Parameters("@PageNum").Value = intPageNumprListPOByCoordinatorPaged.Parameters("@PerPage").Value = intPerPage
SqlConnection1.Open()dr = prListPOByCoordinatorPaged.ExecuteReader(CommandBehavior.CloseConnection)
Me.Pages = prListPOByCoordinatorPaged.Parameters("@Pages").ValueMe.Rows = prListPOByCoordinatorPaged.Parameters("@Rows").Value
If Me.Rows / intPerPage > Me.Pages Then Me.Pages = Me.Pages + 1End If
Return dr
prListPOByCoordinatorPaged.Dispose()SqlConnection1.Close()SqlConnection1.Dispose()
End Function
</code>
It does not crash, it returns my data reader with the correct records. Unfortunately my property values are returned as 0. They should have values.
Anyone know how to do this?
Thanks.
View 1 Replies
View Related
Jul 23, 2005
Hi all!Running the code below in SQL-analyzeer (or through dbExpress) results in NULL.As one might guess I would like the result to be 1. What is wrong? I.e, whywont the result of the SP come back to the caller?CREATE PROCEDURE test@val INTEGER OUTASSELECT @val = 1GODECLARE @val INTEGEREXEC test @valSELECT @val
View 1 Replies
View Related
Sep 25, 2007
I have an SP like this (edited for brevity):
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[TESTING_SP]
@Username MediumText,
@Password MediumText,
@UserKey int OUTPUT,
@RoleKey int OUTPUT,
@UserGroupKey int OUTPUT,
AS
BEGIN
SELECT
@UserKey = UserKey
FROM UserProfile
WHERE Username = @UserName
AND [Password] = @Password
END
I want to execute this sp in Managment Studio (MS) and see what is being returned but I'm getting this error:
Msg 201, Level 16, State 4, Procedure TESTING_SP, Line 0
Procedure 'TESTING_SP' expects parameter '@UserKey', which was not supplied.
How do I set up the output parameters and then select the values in MS for testing purposes?
Thanks a ton for helping a noob.
View 2 Replies
View Related
Mar 25, 2008
I have a set of reports that run just fine with the default parameters (Country = US). The report returns data within 60 seconds. However, if I change the default parameters, say to Country = UK, the report will run and won't seem to stop. The user will be prompted every few minutes to relogin to the domain (which they are not prompted when they first run the report). On the server, the report is taking up 1 of the four CPU's and is using a huge amount of disk paging.
Here's the kicker. If I go in, change the default parameters to Country = UK and deploy the report, it will run in 60 seconds with the new default parameters. Now I try to run the report by changing the country = US and it locks up when it is executed.
Anybody run into something like this?
Rob
View 5 Replies
View Related
Mar 2, 2014
I am trying to create an ssis package with dynamic csv file as output. and out format contains query output.
sample file name:
Unique identifier + query output + systemdate();
The expression is looking like this.
@[User::FilePath] + @[User::FileName] + ".CSV"
-- user filepath is a variable from ssis package. File name is the output from SQL query. using script task i have assigned the values to @[User::FileName] .
When I debugged the script task the value getting properly but same variable am using for Flafile destination. but its not working.
View 3 Replies
View Related
Sep 21, 2004
Hi:
Can anyone tell me if it's possible in SQL Server 2000 to build dynamically a select statement and get some values to variables (simple not tables) from the select ?
Thanks,
Rui Ferreira
View 4 Replies
View Related
Jul 23, 2005
I know this has been dealt with a lot, but I would still reallyappreciate help. Thanks.I am trying to transform this tableYY--ID-Code-R1-R2-R3-R4...R402004-1-101--1--2-3-42004-2-101--2--3-4-2....2005-99-103-4-3-2-1Into a table where the new columns are the count for 4-3-2-1 for everydistinct code in the first table based on year. I will get the yearfrom the user-end(Access). I will then create my report based on theinfo in the new table. Here's what I've tried so far (only for 1stcolumn):CREATE PROCEDURE comptabilisationDYN@colonne varchar(3) '*receives R1, then R2, loop is in vba access*ASDECLARE @SQLStatement varchar(8000)DECLARE @TotalNum4 intDECLARE @TotalNum3 intDECLARE @TotalNum2 intDECLARE @TotalNum1 intSELECT SQLStatement = 'SELECT COUNT(*) FROMdbo.Tbl_Réponses_Étudiants WHERE' + @colonne + '=4 AND YY = @year'EXEC sp_executesql @SQLStatement, N'@TotalNum4 int OUTPUT', @TotalNum4OUTPUT INSERT INTO Comptabilisation(Total4) VALUES (@TotalNum4)GO
View 6 Replies
View Related
May 5, 2008
hello,
I'd like to know how I can grab the output of the following code and use it as variable:
declare @sql nvarchar(25)
set @sql = 'SELECT CURRENT_TIMESTAMP'
EXEC(@sql)
Thank you.
View 2 Replies
View Related
Apr 29, 2014
I am having a bit of trouble writing a query that dynamically outputs what I need.
I have a table that looks like this:
COL1 AAA BBB CCC
CAT 1 3 3
CAT 1 2 2
CAT 1 4 4
DOG 2 3 3
DOG 5 5 5
MICE 1 1 1
I need it to dynamically output based off COL1, the output should look like this. When there are more rows for CAT, it should output more columns. Kind of like merging the columns
COL1 AAA BBB CCC AAA BBB CCC AAA BBB CCC
CAT 1 3 3 1 2 2 1 4 4
DOG 2 3 3 5 5 5 NULL NULL NULL
MICE 1 1 1 NULL NULL NULL NULL NULL NULL
View 3 Replies
View Related
Apr 2, 2015
This is my table.
CREATE TABLE tpivot
([col1] varchar(80), [col2] varchar(80), [col3] varchar(80), [col4] varchar(80), [col5] varchar(80)) ;
INSERT INTO tpivot
([col1], [col2], [col3], [col4], [col5])
VALUES
[code]...
My goal is to turn the table so that the output look like this.
col1 col2 col3 col4 col5
Datum 01.12.2014 02.12.2014 03.12.2014 04.12.2014
EC -204.9 -352.9 -105 -371
Mastercard -88 0 -182 -112.9
Postfinance 0 -79.9 0 -751
VISA -19 -20 -436 -346
I need to have a dynamic pivot since i import the table from a csv that could have different amount of columns each time. I can't even get a static pivot to work.
View 1 Replies
View Related
Nov 15, 2005
The dynamic SQL statements with output parameters, unfortunately, in the documentation are not described.
À) Simple example of the dynamic SQL statement with output parameters
-- dynamic SQL statements expects parameter of type 'ntext/nchar/nvarchar'.
declare @SQLString nvarchar(4000), @ParmDefinition nvarchar(4000)
-- the third parameter passed in the dynamic statement as by output, returns the length of
-- the hypotenuses of a right triangle, two first parameters are lengths of legs of a triangle
declare @nHypotenuse float
select @SQLString = 'select @nHypotenuse = sqrt(square(@nLeg1_of_a_triangle)+square(@nLeg2_of_a_triangle))',
@ParmDefinition = '@nLeg1_of_a_triangle float, @nLeg2_of_a_triangle float, @nHypotenuse float out'
-- we call the dynamic statement in such a way
exec sp_executesql @SQLString, @ParmDefinition, @nLeg1_of_a_triangle = 3.0, @nLeg2_of_a_triangle = 4.0, @nHypotenuse = @nHypotenuse out
-- or in such a way
exec sp_executesql @SQLString, @ParmDefinition, 3.0, 4.0, @nHypotenuse out
select @nHypotenuse -- Displays 5.0
B) Example of usage of the dynamic statement with output parameter and the function GETALLWORDS.
The following stored procedure get all words from a field of the type text or ntext, the word length should not exceed 4000 characters.
CREATE PROCEDURE SP_GETALLWORDSFROMTEXT
@TableName sysname, @FieldIdName sysname, @FieldIdValue sql_variant, @FieldTextName sysname, @cDelimiters nvarchar(256) = NULL
AS
-- this Stored procedure inserts the words from a text field into the table.
-- WORDNUM int – Sequence number of a word
-- WORD nvarchar(4000) – the word
-- STARTOFWORD int – position in the text field, with which the word starts
-- LENGTHOFWORD smallint – length of the word
-- Parameters
-- @TableName name of the table with the text or ntext field
-- @FieldIdName name of Id field
-- @FieldIdValue value of Id field
-- @FieldTextName name of field text or ntext
-- @cDelimiters Specifies one or more optional characters used to separate words in the text field
begin
set nocount on
declare @k int, @wordcount int, @nBegSubString int, @nEndSubString int, @nEndString int, @divisor tinyint, @flag bit, @RetTable bit,
@cString nvarchar(4000), @TypeField varchar(13), @SQLString nvarchar(4000), @ParmDefinition nvarchar(500), @nBegSubString1 smallint, @nEndSubString1 smallint
select @TableName = object_name(object_id(lower(ltrim(rtrim(@TableName))))), @FieldIdName = lower(ltrim(rtrim(@FieldIdName))), @FieldTextName = lower(ltrim(rtrim(@FieldTextName))),
@cDelimiters = isnull(@cDelimiters, nchar(32)+nchar(9)+nchar(10)+nchar(13)), -- if no break string is specified, the function uses spaces, tabs, carriage return and line feed to delimit words.
@nBegSubString = 1, @nEndSubString = 4000, @flag = 0, @RetTable = 0, @wordcount = 0
-- If the temporary table is not created in the calling procedure, we create the temporary table
if object_id( 'tempdb..#GETALLWORDSFROMTEXT') is null
begin
create table #GETALLWORDSFROMTEXT (WORDNUM int, WORD nvarchar(4000), STARTOFWORD int, LENGTHOFWORD smallint)
select @RetTable = 1
end
-- we use the dynamic SQL statement to receive the exact name of text field
-- as we can write names of fields by a call of the given stored procedure in the arbitrary register
-- in the string of parameters definition @ParmDefinition we use a keyword output
-- and by a call of the dynamic SQL statement exec sp_executesql @SQLString, @ParmDefinition, @FieldTextName = @FieldTextName output
-- Also we use a keyword output for definite before parameter
select @SQLString = 'select @FieldTextName = name from syscolumns where id = OBJECT_ID('''+ @TableName+''') and lower(name) = '''+@FieldTextName+''''
select @ParmDefinition = '@FieldTextName sysname output'
exec sp_executesql @SQLString, @ParmDefinition, @FieldTextName = @FieldTextName output
-- we use the dynamic SQL statement to receive the exact name of Id field
select @SQLString = 'select @FieldIdName = name from syscolumns where id = OBJECT_ID('''+ @TableName+''') and lower(name) = '''+@FieldIdName+''''
select @ParmDefinition = '@FieldIdName sysname output'
exec sp_executesql @SQLString, @ParmDefinition, @FieldIdName = @FieldIdName output
-- we use the dynamic SQL statement to receive the type of field (text or ntext)
select @SQLString = 'select @TypeField = name from systypes where xtype = any ( select xtype from syscolumns where id = OBJECT_ID('''+ @TableName+''') and lower(name) = '''+@FieldTextName+''')'
select @ParmDefinition = '@TypeField varchar(13) output'
exec sp_executesql @SQLString, @ParmDefinition, @TypeField = @TypeField output
select @divisor = case @TypeField when 'ntext' then 2 else 1 end -- 2 for unicode
-- we use the dynamic SQL statement to receive a length of the text field
select @SQLString = 'select @nEndString = 1 + datalength('+ @FieldTextName+')/'+cast( @divisor as nchar(1)) +' from '+@TableName +' where '+ @FieldIdName+' = ' +cast(@FieldIdValue as nchar(50))
select @ParmDefinition = '@nEndString int output'
exec sp_executesql @SQLString, @ParmDefinition, @nEndString = @nEndString output
-- We cut the text field into substrings of length no more than 4000 characters and we work with substrings in cycle
while 1 > 0
begin
-- we use the dynamic SQL statement to receive a substring of a type nvarchar(4000) from text field
select @SQLString = 'select @cString = substring('+ @FieldTextName+','+cast( @nBegSubString as nvarchar(20)) +',' +
cast( @nEndSubString - @nBegSubString + 1 as nvarchar(20))+') from '+@TableName +' where '+ @FieldIdName+' = ' +cast(@FieldIdValue as nchar(50))
select @ParmDefinition = '@cString nvarchar(4000) output'
exec sp_executesql @SQLString, @ParmDefinition, @cString = @cString output
select @nBegSubString1 = 1, @nEndSubString1 = @nEndSubString - @nBegSubString +1
while charindex(substring(@cString, @nBegSubString1, 1) COLLATE Latin1_General_BIN, @cDelimiters COLLATE Latin1_General_BIN) > 0 and @nEndSubString >=@nBegSubString -- skip the character not in word, if any
select @nBegSubString = @nBegSubString + 1 , @nBegSubString1 = @nBegSubString1 + 1
while charindex(substring(@cString, @nEndSubString1, 1) COLLATE Latin1_General_BIN, @cDelimiters COLLATE Latin1_General_BIN) = 0 and @nEndSubString >=@nBegSubString -- skip the character in word, if any
select @nEndSubString = @nEndSubString - 1, @nEndSubString1 = @nEndSubString1 - 1
if @nEndSubString >=@nBegSubString
begin
select top 1 @wordcount = WORDNUM from #GETALLWORDSFROMTEXT order by WORDNUM desc
select @cString = substring(@cString, @nBegSubString1, @nEndSubString1-@nBegSubString1+1)
-- we use a function GETALLWORDS which one works with strings of a type nvarchar(4000)
-- we add outcome result in the temporary table
insert into #GETALLWORDSFROMTEXT (WORDNUM, WORD, STARTOFWORD, LENGTHOFWORD)
select (@wordcount+WORDNUM), WORD, (@nBegSubString+STARTOFWORD-1), LENGTHOFWORD from dbo.GETALLWORDS(@cString, @cDelimiters)
select @nBegSubString = @nEndSubString + 1, @nEndSubString = @nEndSubString + 4000
end
else
select @nEndSubString = @nEndSubString + 4000 -- In a case if the substring consists of one delimiter
if @flag = 1
break
if @nEndString <= @nEndSubString
select @flag = 1, @nEndSubString = @nEndString
end
-- If in a calling procedure the table was not created, we show the result
if @RetTable = 1
select * from #GETALLWORDSFROMTEXT
end
GO
Example of the call Stored procedure SP_GETALLWORDSFROMTEXT
declare @cDelimiters nvarchar(256)
select @cDelimiters = '"-,.:!?«»()'+SPACE(1)+CHAR(9)+CHAR(10)+CHAR(13)+CHAR(12)
if object_id( 'tempdb..#GETALLWORDSFROMTEXT') is not null
drop table #GETALLWORDSFROMTEXT
create table #GETALLWORDSFROMTEXT (WORDNUM int, WORD nvarchar(4000), STARTOFWORD int, LENGTHOFWORD smallint)
exec dbo.SP_GETALLWORDSFROMTEXT 'Your Table name', 'Your Id field name', Value of Id field, ' text or ntext field name', @cDelimiters
if object_id( 'tempdb..#GETALLWORDSFROMTEXT') is not null
select * from #GETALLWORDSFROMTEXT
-- GETALLWORDS() User-Defined Function Inserts the words from a string into the table.
-- GETALLWORDS(@cString[, @cDelimiters])
-- Parameters
-- @cString nvarchar(4000) - Specifies the string whose words will be inserted into the table @GETALLWORDS.
-- @cDelimiters nvarchar(256) - Optional. Specifies one or more optional characters used to separate words in @cString.
-- The default delimiters are space, tab, carriage return, and line feed. Note that GETALLWORDS( ) uses each of the characters in @cDelimiters as individual delimiters, not the entire string as a single delimiter.
-- Return Value table
-- Remarks GETALLWORDS() by default assumes that words are delimited by spaces or tabs. If you specify another character as delimiter, this function ignores spaces and tabs and uses only the specified character.
-- Example
-- declare @cString nvarchar(4000)
-- set @cString = 'The default delimiters are space, tab, carriage return, and line feed. If you specify another character as delimiter, this function ignores spaces and tabs and uses only the specified character.'
-- select * from dbo.GETALLWORDS(@cString, default)
-- select * from dbo.GETALLWORDS(@cString, ' ,.')
-- See Also GETWORDNUM() , GETWORDCOUNT() User-Defined Functions
CREATE function GETALLWORDS (@cString nvarchar(4000), @cDelimiters nvarchar(256))
returns @GETALLWORDS table (WORDNUM smallint, WORD nvarchar(4000), STARTOFWORD smallint, LENGTHOFWORD smallint)
begin
declare @k smallint, @wordcount smallint, @nEndString smallint, @BegOfWord smallint, @flag bit
select @k = 1, @wordcount = 1, @BegOfWord = 1, @flag = 0, @cString = isnull(@cString, ''),
@cDelimiters = isnull(@cDelimiters, nchar(32)+nchar(9)+nchar(10)+nchar(13)), -- if no break string is specified, the function uses spaces, tabs, carriage return and line feed to delimit words.
@nEndString = 1 + datalength(@cString) /(case SQL_VARIANT_PROPERTY(@cString,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode
while 1 > 0
begin
if @k - @BegOfWord > 0
begin
insert into @GETALLWORDS (WORDNUM, WORD, STARTOFWORD, LENGTHOFWORD) values( @wordcount, substring(@cString, @BegOfWord, @k-@BegOfWord), @BegOfWord, @k-@BegOfWord ) -- previous word
select @wordcount = @wordcount + 1, @BegOfWord = @k
end
if @flag = 1
break
while charindex(substring(@cString, @k, 1) COLLATE Latin1_General_BIN, @cDelimiters COLLATE Latin1_General_BIN) > 0 and @nEndString > @k -- skip break characters, if any
select @k = @k + 1, @BegOfWord = @BegOfWord + 1
while charindex(substring(@cString, @k, 1) COLLATE Latin1_General_BIN, @cDelimiters COLLATE Latin1_General_BIN) = 0 and @nEndString > @k -- skip the character in the word
select @k = @k + 1
if @k >= @nEndString
select @flag = 1
end
return
end
For more information about string UDFs Transact-SQL please visit the
http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,54,33,27115
View 3 Replies
View Related
Sep 9, 2005
I have a STORED PROC for dynamic T-SQL that returns a OUTPUT varaible of typevarchar . This works fine in query analyzer.
But when I try to get values in asp.net page it does not return anything. No execeptions or errors also. Below is the code snippet
HDConn = CommonMethods.BuildConnection();SqlCommand cmd1 = new SqlCommand("GenerateRowids",HDConn);cmd1.CommandType = CommandType.StoredProcedure;// add parameters for Proccmd1.Parameters.Add("@TableName",SqlDbType.VarChar,50).Value= "MetricsMaster";cmd1.Parameters.Add("@ColumnName",SqlDbType.VarChar,50).Value= "MetricId";cmd1.Parameters.Add("@rowidval",SqlDbType.VarChar,30);cmd1.Parameters["@rowidval"].Direction = ParameterDirection.Output;try{cmd1.ExecuteNonQuery();//the below line does not print the valueResponse.Write(cmd1.Parameters["@rowidval"].Value.ToString());}My stored proc looks like :CREATE PROCEDURE dbo.GenerateRowids@TableName varchar(50),@ColumnName varchar(50),@rowidval varchar(30) outputASBegindeclare @sql nvarchar(4000), @Idval varchar(50)
set @sql = N'select left(MAX(' + @ColumnName +') ,1)+ convert(varchar(5),Cast(SUBSTRING((MAX(' +@ColumnName+ ')),2,(len(MAX(' +@ColumnName+ '))))as int)+1) from ' + @TableNameexec sp_executesql @query = @sql,@params = N'@rowidval varchar OUTPUT', @rowidval = @rowidval OUTPUT ENDGO
View 11 Replies
View Related
Aug 18, 2005
Arvind writes "i want to create a stored procedure returns an OUTPUT variable containing the no. of records given by a query, the query being dynamic.
Preferrably the query should also be passed as a parameter to the stored procedure...If not,it should be constructed in the SP and a Part of the where clause is dependant on the value of another variable passed to the SP.
How should the query be constructed, executed, and then the Count(*) value returned?
"WHERE <condition1> AND <condition 2> ;
"AND <condition 2> " may exist or may not exist in the query; it is dependant."
View 1 Replies
View Related
Feb 5, 2008
I have a procedure that executes a sql statement passed in as a parameter. The parameter variable is
@pSqlStatement.
CREATE PROCEDURE [dbo].[ProcedureTest]
@pSqlStatement nvarchar(4000)
AS
BEGIN
DECLARE @retVal int;
BEGIN TRY
EXECUTE sp_executesql @pSqlStatement, N'@ret int OUTPUT', @retVal OUTPUT;
PRINT 'try @retVal = ' + ISNULL(CAST(@retVal AS varchar), 'null');
END TRY
BEGIN CATCH
PRINT 'catch @retVal = ' + ISNULL(CAST(@retVal AS varchar), 'null');
END CATCH;
END;
Here is an example of this procedure being called:
EXECUTE [dbo].[ProcedureTest]
@pSQLStatement = N'EXECUTE @ret = [log].[Log.SetSeverityLevel] 'debug'
Note that in the sp_executesql statement, the @ret variable from the dynamic SQL gets assigned to the local variable @retVal.
Here's the problem; when the procedure [log].[Log.SetSeverityLevel] that gets dynamically executed does a RAISERROR, the code in [dbo].[ProcedureTest] falls through to the catch block in [dbo].[ProcedureTest] as it should, but the value of @retVal is null in the catch block, when the return code in [log].[Log.SetSeverityLevel] is set to -1.
If you comment out BEGIN TRY / END TRY / BEGIN CATCH / END CATCH in [dbo].[ProcedureTest], the value of @retVal is correctly seen as -1. There's something about the TRY CATCH structure that's blowing away @retVal when an error is raised and it falls through to the CATCH block.
Anybody ever seen this before? Is there a workaround?
Thanks - Dana
View 7 Replies
View Related
Apr 6, 2015
I have a dynamic sql which uses Pivot and returns "technically" variable no. of columns.
Is there a way to store the dynamic sql's output in to a temp table? I don't want to create a temp table with the structure of the output and limit no. of columns hence changing the SP every time I get new Pivot column!!
View 3 Replies
View Related
Jan 27, 2006
HeyLet's say I have some stored procedure that takes as a param userId and it's simple SELECT statement. I want to assign this param and value in my code how to do it ???Jarod
View 2 Replies
View Related
Oct 13, 1999
Can I pass a parameter to a DTS package?
Here is what I am trying to do: Every month we need to import a fixed format text file into one of our tables. The format and location of the file is same every month except for the name. I want to create a DTS package to import it and call this DTS package first thing in a stored procedure(after which I do some processing with this imported data). I want to create the filename in my stored procedure and then call this DTS package to import it.
I am usig DTS as the interface is so much easier and want to avoid bcp :-)
Is it possible?
Thanks in advance,
Nishi
View 1 Replies
View Related
Oct 31, 2007
Hello,
Basically I am trying to do an order by statement using variables.
I have the SQL:
IF @SortOrder = 'ASC'
BEGIN
SELECT DISTINCT D.Code as 'T1', D.CompanyName as 'T2',
S.SpendAmount as 'T3', E.SpendAmount as 'T4',
E.SpendAmount - S.SpendAmount as 'T5',
Case When S.SpendAmount > 0 Then ((E.SpendAmount - S.SpendAmount)/S.SpendAmount) * 100
When S.SpendAmount = 0 Then ((E.SpendAmount - S.SpendAmount)/1)
When E.SpendAmount > 0 Then ((E.SpendAmount - S.SpendAmount)/E.SpendAmount) * 100
When E.SpendAmount = 0 Then ((E.SpendAmount - S.SpendAmount)/1)
END as 'T6'
FROM DivisionData D,
(SELECT Code, SpendAmount FROM DivisionData WHERE Year = @StartYear and Month = @StartMonth and Division = @Division) S,
(SELECT Code, SpendAmount FROM DivisionData WHERE Year = @EndYear and Month = @EndMonth and Division = @Division) E
WHERE D.Division = @Division
AND S.Code = D.Code
AND E.Code = D.Code
END
I have the params @Crit1 @Crit2 .... to @Crit6. These parameters come from a web form and can have the values 'T1' 'T2'... to 'T6' They correspond to the field names I have put in my select statement.
What I want to do is use these params in an Order By statement, enabling the user to select how the fields are ordered.
I have tried using something along the lines of:
ORDER BY
CASE WHEN @Crit1 = 'T1'
THEN 'T1' END
This works as long as I dont use the word DISTINCT in my select statement, otherwise I get the error:
Msg 145, Level 15, State 1, Line 4
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
I do need the distinct... I was wondering if there is a simple way to get round this, or if anyone can point me in the right direction or a article/example of this?
Many thanks
Andrew Rayner
View 6 Replies
View Related
Feb 2, 2004
I've got a pretty straightforward search/results suite with several possible search parameters on the search page. I've been using an inline SQL server query with logic on the results page as shown below. How do I convert this kind of conditional logic to a stored procedure?
Dim strWhere
strWhere = " WHERE dbo.""User"".UID IS NOT NULL "
If Not request.querystring("EmployerID") = "" Then
strWhere = strWhere & " AND dbo.""User"".EmployerID = '" & replace(request.querystring("EmployerID"),"'","''") & "'"
End If
If Not request.querystring("AccountNumber") = "" Then
strWhere = strWhere & " AND dbo.""User"".AccountNumber = '" & replace(request.querystring("AccountNumber"),"'","''") & "'"
End If
If Not request.querystring("LastName") = "" Then
strWhere = strWhere & " AND dbo.""User"".LastName = '" & replace(request.querystring("LastName"),"'","''") & "'"
End If
If Not request.querystring("FirstName") = "" Then
strWhere = strWhere & " AND dbo.""User"".FirstName = '" & replace(request.querystring("FirstName"),"'","''") & "'"
End If
DBConn = New OleDbConnection(ConfigurationSettings.AppSettings("ConnStr"))
DBCommand = New OleDbDataAdapter _
("SELECT dbo.""User"".*, Convert(varchar(16), dbo.""User"".DateEntered, 101) AS Created, dbo.Employer.CompanyName, dbo.AccessLevel.AccessLevel AS AccessLevelName FROM dbo.""User"" INNER Join dbo.Employer ON dbo.""User"".EmployerID = dbo.Employer.EmployerID INNER JOIN dbo.AccessLevel ON dbo.""User"".AccessLevel = dbo.AccessLevel.AccessLevelID " & strWhere & " ORDER BY " & strSortField,DBConn)
Thanks in advance?
View 3 Replies
View Related
Feb 1, 2005
Hi all,
To optimise certain functionality in my app I want to do a few inserts after another by executing the whole lot in one procedure. I want to use the return param from some procedures (RETURN @@IDENTITY) as input for some of the other procedures.
I am getting errors when I compile the proc:
Line 10: Incorrect syntax near the keyword "EXEC"
Same error on Line 11...
CREATE PROCEDURE addTemplateDetail
@TemplateID int,
@GroupNameID int,
@SubGroupNameID int=null,
@MethodID int,
@AnalyteID int
AS
DECLARE @TemplateGroupNameID int
DECLARE @TemplateMethodID int
SET @TemplateGroupNameID=(EXEC addTemplateGroupName @TemplateID, @GroupNameID)
SET @TemplateMethodID=(EXEC addTemplateMethod @TemplateGroupNameID, @SubGroupNameID, @MethodID)
EXEC addTemplateAnalyte(@TemplateMethodID,@AnalyteID)
I also tried adding brackets around the input params for the EXECed sp's, but that generated even more errors...
Can somebody see what I am doing wrong?
TIA.
View 1 Replies
View Related
May 7, 2008
Hi
I am trying to execute a stored procedure from another stored procedure by passing parameters. any idea please.
I have tried with EXECUTE PROC dbo.dxGetMemberActivityData (@author) RETURNING_VALUES RESULT
but I am getrting incorrect sytax near PROC
View 2 Replies
View Related
Feb 6, 2007
Hi... I am trying to display a parameter in my report.. the parameter can have up to 5 chocies... if all 5 are checked I want to display all 5..
I know how to trick it and use: Parameters!Country.Value(0)&Parameters!Country.Value(1) etc
Is there a way to do this that I dont have to have from (0) to (5)..
another thing... when you only choose 2 of the 5 params the rest show errors.. (#Error)
Thanks for help... and Happy Reporting !!
View 1 Replies
View Related
Jul 13, 2004
This is not obvious to me...
As far as i can tell, you cannot pass an array (or structured) parameter to a stored procedure...
Ok, this means when you have to store data for an item and its sub-items (e.g. a product and its - say- version specific infos) you cannot code all the logic into a single procedure. You need to code it into your DAL, where you first insert then loop to sub-insert...
Is this correct?
Is there any other way to approach the problem?
Thanks a lot. -julio
View 3 Replies
View Related
Oct 8, 2014
How to pass parameter values to Stored Procedure using Openquery ?
DECLARE @CenterNumber nvarchar(8)
DECLARE @CenterName nvarchar(100)
DECLARE @tblLeads table(
LeadCount int)
SET @CenterNumber = '98454152'
SET @CenterName = 'neck'
[code]....
View 3 Replies
View Related
Jun 20, 2000
I am getting an insert error with the following SP. I don't have to pass the CampID because it is an IDENTITY field. The error says "number of supplied values does not match table definition."
Do I pass in the CampID to the SP and allow nulls? Thanks in advance
Nathan
CREATE PROCEDURE sp_CampReg1
@UserNamevarchar(15),
@Passwordvarchar(15),
@CampNamevarchar(50),
@Hostvarchar(50),
@Directorvarchar(25),
@Contactvarchar(25),
@Addressvarchar(30),
@Cityvarchar(25),
@Statevarchar(20),
@Zipvarchar(15),
@Countryvarchar(20) = NULL,
@Phonevarchar(20) = NULL,
@AlternatePhonevarchar(20) = NULL,
@Faxvarchar(20) = NULL,
@ContactEmailvarchar(20),
@AdminEmailvarchar(20),
@URLvarchar(50) = NULL,
@CampTypeint,
@CampProfileText =NULL,
@CampIDintOUTPUT
AS
INSERT INTO TempCampSignup
VALUES
(
@UserName,
@Password,
@CampName,
@Host,
@Director,
@Contact,
@Address,
@City,
@State,
@Zip,
@Country,
@Phone,
@AlternatePhone,
@Fax,
@ContactEmail,
@AdminEmail,
@URL,
@CampType,
@CampProfile
)
SELECT @CampID = @@IDENTITY
View 1 Replies
View Related
Mar 19, 2003
Can I use the result of a scalar function as the parameter for a stored procedure? ie
exec [dbo].[usp_insert_into_table]
@integer = [dbo].[uf_getAnIDfromName]('PLAYER')
where the @integer parameter expects an integer and the user function uf_getAnIDFromName returns an integer related to the 'PLAYER' name.
View 2 Replies
View Related
Oct 27, 2005
Hi all,I'd like to put together a SQL statement that will take the name of astored procedure as a param, and return that SP's parameters.I'm writing a test application, and I'd like to wrte a generator tosave myself some time, but I can't seem to figure out how to get theparams from a SP. Any help would be appreciated.Thanks in advance,Craig
View 2 Replies
View Related
Jul 24, 2007
Hi,
I have a SSIS package with a Sequence which Contains a Webservice Task, in the input section of this task i want to pass a User Variable as Parameter for my webmethod. but it doesn´t work, it allways sends the variable definition as string "@[User::Filename]". so i searched Microsoft Technet how to pass User Variables in Webservice Tasks and found this site: http://technet.microsoft.com/en-us/library/ms187617.aspx
which says :
"
Variable
Select the check boxes to use variables to provide inputs. "
but there is no such checkbox on the input page of my Webservice Task... there is just the Value column which i can edit... but as mentioned before when i try to set the value to a variable it doesn work
i tried the following strings in the value column:
@[User::Filename]
@Filename
User::Filename
@User::Filename
any ideas?
thanks for your help
bye
as
View 5 Replies
View Related
Mar 15, 2007
hi
HHello!, the following 3 webmethods still work after i switched them to WCF but now it never accepts my parameters. i didn't change anything in reporting services, but i have a feeling it's not sending the params to the service anymore.
is there an operation contract attribute change i need to make to require the datetime be provided by RS?
here's the schema provided by my serviec wsdl (the relevant portion)
- <xs:element name="EeoByAssignmentReport">
- <xs:complexType>
- <xs:sequence>
<xs:element minOccurs="0" name="start" type="xs:dateTime" />
<xs:element minOccurs="0" name="end" type="xs:dateTime" />
</xs:sequence>
</xs:complexType>
</xs:element>
- <xs:element name="EeoByAssignmentReportResponse">
- <xs:complexType>
- <xs:sequence>
- <xs:element minOccurs="0" name="EeoByAssignmentReportResult" nillable="true">
- <xs:complexType>
- <xs:annotation>
- <xs:appinfo>
<ActualType Name="DataSet" Namespace="http://schemas.datacontract.org/2004/07/System.Data" xmlns="http://schemas.microsoft.com/2003/10/Serialization/" />
</xs:appinfo>
</xs:annotation>
- <xs:sequence>
<xs:element ref="xs:schema" />
<xs:any />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
- <xs:element name="EeoByPersonReport">
- <xs:complexType>
- <xs:sequence>
<xs:element minOccurs="0" name="start" type="xs:dateTime" />
<xs:element minOccurs="0" name="end" type="xs:dateTime" />
</xs:sequence>
</xs:complexType>
</xs:element>
- <xs:element name="EeoByPersonReportResponse">
- <xs:complexType>
- <xs:sequence>
- <xs:element minOccurs="0" name="EeoByPersonReportResult" nillable="true">
- <xs:complexType>
- <xs:annotation>
- <xs:appinfo>
<ActualType Name="DataSet" Namespace="http://schemas.datacontract.org/2004/07/System.Data" xmlns="http://schemas.microsoft.com/2003/10/Serialization/" />
</xs:appinfo>
</xs:annotation>
- <xs:sequence>
<xs:element ref="xs:schema" />
<xs:any />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
- <xs:element name="EeoCountsReport">
- <xs:complexType>
- <xs:sequence>
<xs:element minOccurs="0" name="start" type="xs:dateTime" />
<xs:element minOccurs="0" name="end" type="xs:dateTime" />
</xs:sequence>
</xs:complexType>
</xs:element>
- <xs:element name="EeoCountsReportResponse">
- <xs:complexType>
- <xs:sequence>
- <xs:element minOccurs="0" name="EeoCountsReportResult" nillable="true">
- <xs:complexType>
- <xs:annotation>
- <xs:appinfo>
<ActualType Name="DataSet" Namespace="http://schemas.datacontract.org/2004/07/System.Data" xmlns="http://schemas.microsoft.com/2003/10/Serialization/" />
</xs:appinfo>
</xs:annotation>
- <xs:sequence>
<xs:element ref="xs:schema" />
<xs:any />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
View 1 Replies
View Related
Oct 26, 2006
Hi all,
[Reporting Services 2005]
I have reports delivered by email subscription. My "date from - to" parameters are set to "default" as "=Today()" and "=Now()". Report is delivered fine but I have problems with "link" to that report (attached to email). RS don't add my default parameter values to this link so every time user click this link, report is generated with new "date from" and "date to" and I want to generate exact the same report (same parameters) as in the email.
Is there a way to send default parameter values as static values in email delivered report link ?
Thanks
Maciej
View 4 Replies
View Related
Mar 20, 2006
All,
I have the following :
ALTER PROCEDURE [dbo].[sp_FindNameJon]
@NameName varchar(50)='',
@NameAddress varchar(50)='',
@NameCity varchar(50)='',
@NameState varchar(2)='',
@NameZip varchar(15)='',
@NamePhone varchar(25)='',
@NameTypeId int=0,
@BureauId int,
@Page int=1,
@Count int=100000
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @SqlString nvarchar(3000),@SelectClause nvarchar(1000), @FromClause nvarchar(1000),@WhereClause nvarchar(1000)
DECLARE @ParentSqlString nvarchar(4000)
DECLARE @Start int, @End int
INSERT into aaJonTemp values (@Page, 'here2', @NameCity);
And inside of aaJonTemp, I have the following :
NULL
here2
NULL
NULL
here2
NULL
How is this possible? If @Page or @NameCity is NULL, how come it doesn't default to a value in the stored proc?
Thx
jonpfl
View 1 Replies
View Related
