SQL Server 2012 :: Concat Parameter In A Message

Jun 2, 2014

I have a procedure (a) where i call another procedure (b) passing @message as a parameter to procedure B.

Ex:
Create procedure a
as
Begin
declare @prevyear
exec b @message = 'avvasdva' + cast(@prevyear as varchar)
End

When i execute above procedure, i get error at + sign i.e. @message parameter. how can i concatenate string with another parameter when passing parameters

View 3 Replies


ADVERTISEMENT

SQL Server 2012 :: SSRS - Display A Message When User Enter Wrong Parameter?

Mar 12, 2015

Issue #1 I have a report that takes 8 character parameter, A error message needs to popped out if user enters parameter less than 8 character.

Issue #2 I have a report with Tablix. A message needs to display if Tablix returns no Rows.

View 1 Replies View Related

SQL 2012 :: Group Concat To Make 2 Rows Into 1?

Oct 14, 2014

I have 2 different types of data for the same key. My queries renders these results into 2 rows. I'd like them to be one row.

Example

DOG Name_a Color_a Age_a Name_B Color_B Age_B
Boxer BO Fawn 1
Boxer Maggie Brindle 4
Lab Jackson Yellow 2
Lab Sandie Black 3

The result I want

DOG Name_a Color_a Age_a Name_B Color_B Age_B
Boxer BO Fawn 1 Maggie Brindle 4
Lab Jackson Yellow 2 Sandie Black 3

View 1 Replies View Related

SQL Server 2012 :: Set Default Parameter For Function Parameter?

Jan 13, 2014

I want to set the default parameters for a function. I;d like to set the date start date to current date and end date for the last 90 days. how to make this work?

Create Function HR.Equipment
(
@startdate Date =(Convert(Date,DATEADD(DAY,-1,GETDATE())),
@enddate Date = (Convert(Date,@StartDate-90)
)
RETURNS TABLE AS RETURN
(
SELECT
EquipID,
EmpName,
IssueDate
FROM HR.Equipment
WHERE IssueDate <=@StartDate and IssueDate >=@EndDate
)
GO

View 5 Replies View Related

SQL Server 2012 :: RAISERROR Not Showing Custom Message In Job History

Apr 1, 2014

So I have two steps in my job that check conditions and call RAISERROR to gracefully complete the job if those conditions are met (meaning I only want the final step to run in a specific situation). Works great, except for some reason my messages are not making it into the log. It is appending the SQLSTATE, and a default error instead of anything that I'm putting into RAISERROR.

View 2 Replies View Related

SQL Server 2012 :: Error Message - Aggregate Function And Group By Clause

Feb 19, 2014

I'm trying to write a query to select various columns from 3 tables. In the where clause I use a set of conditions, but most important condition is that I only want to see all results from the different columns where the ph.ProdHeaderDossierCode contains at least 25 lines of processed hours. I tried this with group by and having, but I constant get error messages on all other columns that I want to see: "is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause". How can I make this so I can see all information I need?

Here is my code so far:

selectph.CalculatedTotalTime,
ph.ProdHeaderDossierCode,
ph.MachGrpCode,
ph.EmpId,
pd.PartCode
fromdbo.T_ProcessedHour ph,

[Code] ....

View 8 Replies View Related

SQL Server 2012 :: How To Order By Using Parameter Value

Jul 15, 2014

I have a stored procedure that I would like to order by the order of the parameters it takes in starting for current item number 1, prior item number 1, current item number 2, prior item number 2, and so on.

currently I am ordering it by:

c.CurrentItemNumber
, p.PriorItemNumber

among other fields, but I would like to replace the part above with the parameters 1 through 5 (current and prior).

Is this possible?

This is my stored procedure for reference:

ALTER PROCEDURE [cost].[Ingredient_Cost_Comparison]
(
@CurrentSalesQuoteNumberNVARCHAR(20)
,@PriorSalesQuoteNumberNVARCHAR(20)
,@CurrentItemNumber1NVARCHAR(20)
,@PriorItemNumber1NVARCHAR(20)
,@CurrentItemNumber2NVARCHAR(20)

[Code] ....

View 5 Replies View Related

SQL Server 2012 :: Pass Parameter Value To CTE

Jul 29, 2015

In some t-sql 2012 that I am using, I am using the following on 5 separate merge statements.

USING
(select LKC.comboID,LKC.lockID,LKC.seq,A.lockCombo2,A.schoolnumber,LKR.lockerId
from
[LockerPopulation] A
JOIN TST.dbo.School SCH ON A.schoolnumber = SCH.type
JOIN TST.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID AND A.lockerNumber =
LKR.number
JOIN TST.dbo.Lock LK ON LKR.lockID = LK.lockID
JOIN TST.dbo.LockCombination LKC ON LK.lockID = LKC.lockID
and LKC.seq = 1
) AS LKC2 (comboID,lockID,seq,combo)

What is different, is the value of LKC.seq = 1 as listed below. I am using 5 separate ctes

and the only value that changes is the LKC.seq number being a value between 1 and 5. Thus

can you pass a parameter value to the CTE that I just listed above? If so, would you show me

the t-sql to accomplish this goal?

View 2 Replies View Related

SQL Server 2012 :: Default Value For Boolean Parameter

Sep 23, 2015

I have got a stored procedure with a parameter on a boolean field. When the parameter is passed down I must retrieve records according to the boolean value. That is if it is true retrieving records with the true in that field, if it is false retrieving records with false. And if no parameters is passed down just retrieve all records either with true or false. I have done something similar with integer fields and it works but in that case I wasn't able to make it working.

See at the following sample I am expecting when executing the 3rd time my below to return 4 and it returns 0

CREATE TABLE #temp
( Id int, Name char(30), YesNo bit )
INSERT INTO #temp (Id, Name,YesNo)
Select 1, 'a', 0
INSERT INTO #temp (Id, Name,YesNo)

[Code] ....

View 5 Replies View Related

SQL Server 2012 :: Call A Split Parameter Function

Sep 15, 2014

In t-sql 2012, the followinng sql works fine when I declare @reportID.

IF @reportID <> 0
BEGIN
SELECT 'Students report 1' AS selectRptName, 1 AS rptNumValue
UNION
SELECT 'Students report 2', 2
UNION

[code]...

However when I use the sql above in an ssrs 2012 report, the query does not work since the @reportID parameter can have 0, 1, or up to 200 values.Thus I am thinking of calling the following following function to split out the parameter values:

FUNCTION [dbo].[fn_splitString]
(
@listString VARCHAR(MAX)
)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
(
SELECT SUBSTRING(l.listString, sn.Num + 1, CHARINDEX(',', l.listString, sn.Num + 1) - sn.Num - 1) _id
FROM (SELECT ',' + LTRIM(RTRIM(@listString)) + ',' AS listString) l
CROSS JOIN dbo.sequenceNumbers sn
WHERE sn.Num < LEN(l.listString)
AND SUBSTRING(l.listString, sn.Num, 1) = ','
)

GO

how to remove the @reportID <> 0 t-sql above and replace by calling the fn_splitString function?

View 2 Replies View Related

SQL Server 2012 :: Dual Purpose OUTPUT Parameter

Oct 21, 2014

Assume I built a stored proc (dbo.testproc) that will return the OUTPUT parameter @RandomInteger.

I could pass a specific value for the parameter...

EXEC dbo.testproc 7

Or I could return a value from the proc...

DECLARE @ReturnInteger
EXEC dbo.testproc @RandomInteger = @ReturnInteger OUTPUT
SELECT @ReturnInteger

But I want to do both which, if this actually worked, might look like this...

DECLARE @RandomInteger
SET @RandomInteger = 7
EXEC dbo.testproc @RandomInteger = @ReturnInteger OUTPUT
SELECT @ReturnInteger

I want to pass a specific value and return the result from the proc. Do I need to use two parameters for this?

View 7 Replies View Related

SQL Server 2012 :: Loop Through Each Row Of Table Valued Parameter

Jun 16, 2015

I need looping through each row of Table valued parameter. I have user defined type

CREATE TYPE [dbo].[PCS_SPEC_ATTR_VALUE] AS TABLE(
[ATTR_NAME] [varchar](256) NULL,
[ATTR_VAL] [varchar](4000) NULL
)

I am using this type in my procedure like

@P_TYPE VARCHAR(4000),
@P_SCOPE VARCHAR(4000),
@P_PART_CLS_ATTR PCS_SPEC_ATTR_VALUE readonly

I am using P_PART_CLS_ATTR as input where I can insert the data as attr_name and attr_value. I want to access each row of inserted values like row by row. I need take attribute name and value process them and return for inserting.. How to access the values in row by row?

View 4 Replies View Related

SQL Server 2012 :: Store Multi Values In One Parameter?

Sep 29, 2015

i design SP for insert data in 2 tables i need to store list of array in one parameter to complete my query i try the table value but it`s not good for me because table value is readonly and i need to insert data with list of array .....

View 5 Replies View Related

SQL Server 2012 :: Input Parameter For Store Procedure Like In Statement Value 1 / Value 2

Jun 20, 2014

How can I input parameter for Store Procedure like In ('Value1','Value2')

Example :
Create procedure GetUsers
@Users nvarchar(200)
as
Select * from Users where UserID in (@Users)

View 6 Replies View Related

SQL Server 2012 :: Passing Date Parameter To Stored Procedure?

Oct 9, 2014

I am trying to pass a date parameter to a stored procedure.

I pass the actual date as a string but keep getting errors that the string variable cannot be converted to a date whatever I do.

Basically, this works:

DECLARE @DDate DATETIME
SET @DDate = convert(datetime, '2004-01-01',101 )

But this returns an error:

DECLARE @strdate VARCHAR
SET @strdate = '2004-01-01'
DECLARE @DDate DATETIME
SET @DDate = convert(datetime, @strdate,101 )

What am I doing wrong?

View 8 Replies View Related

SQL Server 2012 :: How To Modify ORDER BY Clause At Runtime Using Parameter

Jun 4, 2015

the code below works (this is only a quick dumbed down version of the actual code, it might not work 100% for all cases). Is it at all possible to exploit the functions that were added to SSQL since v. 2005 to simplify this code ?

In SSRS, a parameter allows the user to create a list of invoices (from CRM) to be ordered in any of the following ways the user prefers:

'Document Date (most recent date first)'
'Document Number (highest number first)'
'Document Date (most recent first) and Number'
'Document Number (lowest number first)'

The invoices have a (supposedly) sequential identity-generated number. However Accounting may want to set a different date than the creation date on some invoices. So there is no way the invoice numbers will be in the same sequence as the invoice dates.

So I just created the "sorting fields" - they appear as junk in the output dataset (just do not drop them in the SSRS tablix - they have to be part of the SELECT statement to be usable in the ORDER BY clause.

The code is:

DECLARE @ls_OrderBy varchar(80)
--'Document Number (highest number first)'
--'Customer and Document Date (most recent date first)'
--'Customer and Document Number (highest number first)'
--'Document Date (most recent first) and Number'

[code]....

View 9 Replies View Related

SQL Server 2012 :: Can Excel Be Passed As Stored Procedure Parameter

Sep 29, 2015

Currently i am using SQL Server 2012 Import/Export Wizard to upload data to sql tables manually. However i was trying to write a procedure to update that table. and on the time of execution, if i can pass excel. Is there any way to pass excel to stored procedure parameter?

View 1 Replies View Related

Error Message In Set Datefirst 7 And Report Parameter

Nov 27, 2007



Hi,

I am setting my Dataset in SSRS to define the First Day of the week to Sunday.
So In the Dataset I put this scripting :
==================================
SET DATEFIRST 7;
SELECT city, country, datepart(wk,transactdate) as WeekNo from CatalogTable
==================================
The select statement above working very fine in the report.

But then, when I put in the Parameter which I have set in Report Parameter for a field called Service then I will get the problem.
For example the scripting below : ==================================
SET DATEFIRST 7;
SELECT city, country, datepart(wk,transactdate) as WeekNo from CatalogTable
where Service IN (@PService)
==================================
I have defined @PService in Report Parameter, and in the Parameter Tab in Dataset inside SSRS.
But it kept prompting me with the eror message :
Error Source : .Net SqlClient Data Provider
Error Message : Must declare the scalar variable "@PService".

When I remove the first line SET DATEFIRST 7; then my query working fine.

It seems SSRS cannot accept SET statement, whenever we insert the parameter in there.
Is there any workaround on this ?

Appreciate a lot for your help.

cheers,
Tanipar




View 1 Replies View Related

Transact SQL :: How To Set Error Message To Output Parameter

Aug 31, 2015

In Sql Server 2008 R2, I am creating a procedure with multiple transactions in it. Try..Catch Block is used for each transaction. I use output parameter to catch the error code and message since it will be caught by the main program. But When there is errors the output parameter are not correct set to the error message and code?

create procedure xxx (@P_Return_Status VARCHAR(1) OUTPUT, @P_Error_Code INT OUTPUT,)
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION TR1
.....
COMMIT TRANSACTIOn TR1
END TRY

[code].....

View 4 Replies View Related

SQL Server 2012 :: Assigning Multiple Rows To A Single Variable Parameter

Nov 27, 2014

The following works in query if I specify one student (PlanDetailUID) when running query. If I try to specify multiple students (PlanDetailUID) when running query, I get variable cannot take multiple entries. I assume I would need to replace (variables) in PART 2 with (case statements / using select everywhere) to get around the issue or is there a better way ?

CREATE TABLE #AWP (
[TransDate] [datetime] NULL,
[Description] [varchar](1000) NULL,
[Amount] [float] NULL,
[TotalDueNow] [float] NULL,

[code]....

View 4 Replies View Related

SQL Server 2012 :: Parameter Datatypes Linked To Underlying Table Column Datatype

Jun 5, 2014

In Oracle when i create any procedure i define parameter datatype linked to under lying table.

For ex
create procedure testprocedure
(param1 customer.name%type,
param2 customer.salary%type )

Here i have defined param1 and param2 with datatype of name and salary of customer table respectively.

This way i do not need to worry about modifying param1 and param2 datatypes when datatypes of name & salary of customer table changes in future.

How can i accomplish this in SQL server.

View 2 Replies View Related

SQL Server 2012 :: User Defined Functions - Include Comments In Parameter Intellisense?

Aug 22, 2014

If you use the LEFT() function for example it provides intellisense support for some of the required parameters.

Is there a way to get that same descriptive text in user defined functions?

Some comment block you define when creating the function?

View 4 Replies View Related

SQL Server 2012 :: Create Variable In Select Query And Use It In Where Clause To Pass The Parameter

Sep 9, 2014

I am writing a stored procedure and have a query where I create a variable from other table

Declare @Sem varchar (12) Null
@Decision varchar(1) Null
Select emplid,name, Semester
Decision1=(select * from tbldecision where reader=1)
Decision2=(select * from tbldecision where reader=2)
Where Semester=@Sem
And Decision1=@Decision

But I am getting error for Decision1 , Decision2. How can I do that.

View 6 Replies View Related

Trigger :: Xp_smtp_sendmail :: Parameter Value In Message/subject :: Magic Table

Oct 23, 2007

OK so now I'm setting up a trigger to email info to different people based on values INSERTed into one of my tables. I'm using an IF statement to determine who I'm giong to send the mail to, but I don't really understand how to include the data (parameters) in my message. Thanks for any help. Here's what I have so far.





Code Block
ALTER TRIGGER [smallin].[trig_test]
ON [smallin].[DATALIST]
AFTER INSERT
AS
declare @rc int,
@post bit,
@pre bit
SELECT @post = [POST], @pre = [PRE] FROM inserted
IF @post = 1
exec @rc = master.dbo.xp_smtp_sendmail
@FROM = N'ln.li@mydomain.com',
@FROM_NAME = N'Alert Mailer',
@TO = N's.mallin@mydomain.com',
@replyto = N'ln.li@mydomain.com',
@CC = N'',
@BCC = N'',
@priority = N'NORMAL',
@subject = N'This is post data',
@message = N'Goodbye MAPI and Outlook',
@messagefile = N'',
@type = N'text/plain',
@attachment = N'',
@attachments = N'',
@codepage = 0,
@server = N'exchange.mydomain.com'

ELSE IF @pre = 1
exec @rc = master.dbo.xp_smtp_sendmail
@FROM = N'ln.li@mydomain.com',
@FROM_NAME = N'Alert Mailer',
@TO = N's.mallin@mydomain.com',
@replyto = N'ln.li@mydomain.com',
@CC = N'',
@BCC = N'',
@priority = N'NORMAL',
@subject = N'This is pre data',
@message = N'Goodbye MAPI and Outlook',
@messagefile = N'',
@type = N'text/plain',
@attachment = N'',
@attachments = N'',
@codepage = 0,
@server = N'exchange.mydomain.com'





View 5 Replies View Related

I Am Getting This Message System.Data.SqlClient.SqlException: Xp_sendmail: Procedure Expects Parameter @user, Which Was Not Supplied.

Jun 21, 2005

I have looked all over my code and can not find anywhere that I am referencing the xp_sendmail procedure! Here is all the code<code>With sqlCmdUpdateParticipants
.Parameters("@ClassID").Value = ddlClass.SelectedItem.Value
.Parameters("@Person").Value = tbName.Text()
End With
cnCapMaster.Open()
sqlCmdUpdateParticipants.ExecuteNonQuery()
cnCapMaster.Close()</code>I am just getting a couple values and and inserting them into the database.  the insert works then I get darn error message.  This code worked at one time but it has been about 2 years sense I worked on it so who knows what might have happened sense then.Thanks,Bryan

View 6 Replies View Related

SQL 2012 :: Adding Required Message Popup To SSRS Home Page

Sep 26, 2014

Our Information Assurance (IA) office wants us to add a warning message popup box to our SQL 2012 SSRS server.The purpose is to force users to acknowledge the warning box popup before they get into to the SSRS main page.The message box would contain text stating Privacy Act information and or DoD monitoring consent...

View 1 Replies View Related

SQL Server 2012 :: Table Returning Function With Input Table Name As Parameter

Nov 19, 2014

I'm using SS 2012.

I started with an inline table returning function with a hard coded input table name. This works fine, but my boss wants me to generalize the function, to give it in input table parameter. That's where I'm running into problems.

In one forum, someone suggested that an input parameter for a table is possible in 2012, and the example I saw used "sysname" as the parameter type. It didn't like that. I tried "table" for the parameter type. It didn't like that.

The other suggestion was to use dynamic sql, which I assume means I can no longer use an inline function.

This means switching to the multi-line function, which I will if I have to, but those are more tedious.

Any syntax for using the inline function to accomplish this, or am I stuck with multi-line?

A simple example of what I'm trying to do is below:

Create FUNCTION [CSH388102].[fnTest]
(
-- Add the parameters for the function here
@Source_Tbl sysname
)
RETURNS TABLE
AS
RETURN
(
select @Source_Tbl.yr from @Source_Tbl
)

Error I get is:

Msg 1087, Level 16, State 1, Procedure fnTest, Line 12
Must declare the table variable "@Source_Tbl".

If I use "table" as the parameter type, it gives me:

Msg 156, Level 15, State 1, Procedure fnTest, Line 4
Incorrect syntax near the keyword 'table'.
Msg 137, Level 15, State 2, Procedure fnTest, Line 12
Must declare the scalar variable "@Source_Tbl".

The input table can have several thousand rows.

View 9 Replies View Related

SQL Server 2012 :: Use Of Default Keyword As Parameter Default - What Value Is It

Aug 11, 2015

@pvColumnName  VARCHAR(100) = Default,  

However, I am unable to determine what is the value for Default. Is it '' ?

Default is not permitted as a constant - below fails to parse:

WHERE t2.TABLE_TYPE = 'BASE TABLE'
AND (@pvColumnName = Default OR t1.[COLUMN_NAME] Like @vColumnName)

View 4 Replies View Related

Reporting Services :: Error Message When Trying To Open Reports On SCCM 2012 Console And IE Browser

Mar 5, 2013

I have finally have reporting services up and running. A list of all built-in reports have been populated under Monitoring>Reports but when I click on them I get the results shown in the screen shot attached. Below is the error I get when I try to open any report from my IE browser. I have confirmed SSRS is running on my SQL server. I actually had to start it in order to get the list of reports to populate becasue it was stopped. An error has occurred during report processing. (rsProcessingAborted) Cannot impersonate user for data source 'AutoGen__5C6358F2_4BB6_4a1b_A16E_8D96795D8602_'. (rsErrorIm personatingUser) Log on failed. Ensure the user name and password are correct.

View 4 Replies View Related

Concat

Apr 19, 2007

Hi peso, hi KH, Hi everyone....

Declare @day int
set @day = 3

select right('0' + @day, 2)

My expected result is : 03
If single integer - will append in the right a '0'.
If 2 digit integer - will return the actual value.

Thanks.

-Ron-

"If you can only access one site on the Internet, make it SQLTeam!"

View 4 Replies View Related

Concat?

Jan 29, 2008

I am trying to return rows from a query and I want to display the information from 2 columns into one. Here is my query:

SELECT TOP (100) PERCENT dbo.CASES.CaseID, dbo.CASENUMBERTYPES.CaseNumTypeDesc, dbo.CASENUMBERS.CaseNumber,
dbo.SCANNEDDOCUMENTS.TopicID, dbo.EVENTS.EventTypeID, dbo.EVENTS.ev_StartDate, dbo.SCANNEDDOCUMENTS.Doc
FROM dbo.SCANNEDDOCUMENTS INNER JOIN
dbo.CASES ON dbo.SCANNEDDOCUMENTS.CaseID = dbo.CASES.CaseID INNER JOIN
dbo.CASENUMBERS ON dbo.CASES.CaseID = dbo.CASENUMBERS.CaseID INNER JOIN
dbo.CASENUMBERTYPES ON dbo.CASENUMBERS.CaseNumTypeID = dbo.CASENUMBERTYPES.CaseNumTypeID INNER JOIN
dbo.EVENTS ON dbo.CASES.CaseID = dbo.EVENTS.CaseID
WHERE (dbo.CASENUMBERTYPES.CaseNumTypeDesc IN ('cc', 'dc')) AND (dbo.EVENTS.EventTypeID IN (7048, 6467)) AND
(dbo.SCANNEDDOCUMENTS.TopicID IN (2, 3, 4)) AND (dbo.EVENTS.ev_StartDate > CONVERT(DATETIME, '2008-01-13 00:00:00', 102)) AND
(dbo.EVENTS.ev_StartDate < CONVERT(DATETIME, '2008-01-19 00:00:00', 102))
ORDER BY dbo.CASENUMBERTYPES.CaseNumTypeDesc, dbo.CASENUMBERS.CaseNumber

I get this for my return






1526959
CC
228762
2
7048
1/16/2008 12:00:00 AM
<Binary data>
CC is my CASENUMBERTYPES and the next column (228762) is CASENUMBERS

How can I get it to return it to where the CASENUMBERTYPES and CASENUMBERS show together in one column?

View 7 Replies View Related

Concat Rows Help

Jun 11, 2008

Hi there, I'm trying to concat the results of a select statement into a single row separated by commas without duplicates. The query and output is something like this:SELECT Specification FROM Cars WHERE Model = 112Which would return something like:Specification------------------Model 1Model 2Model 2Model 4 What I'd like is these to be combined into a single row such as:Specification
------------------Model 1, Model 2, Model 4 

View 12 Replies View Related

Ok To Concat A Uniqueidentifier?

Jul 8, 2004

I want to use a NEWID() to generate order numbers, but i dont want to give customers the long uniqueID. so im wondering if i concatinate it to 8 characters, if that would be safe or not...

thx in adv

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved