|
You have several options for this and they all have their downsides. Here's few.
One way is to create the statement and concatenate the parameter values to a string variable. After that you can execute the contents of the variable using EXEC[^]. This may lead to problems when dates, numbers etc are used in the parameters.
Another option is to create a permanent or temporary table and insert the parameter values to this table and join the openquery to this table. Of course you can use other conditions also (exists, 'column returned by openquery' < 'value in temp table' etc etc). Now you can build a data-type safe solution but you may encounter performance problems.
|
|
|
|
|
hi,
when we pass the parameter , not need of the quotes.
for eg:
without param
select * from employee where ename='sss';
with param
Declare en as varchar(5);
set en='wowow';
select * from employee where ename=@en;
Good luck.
|
|
|
|
|
Hi, guys
We encountered a SQL server error message saying, "String or binary data would be truncated". I know it's because some new records to be inserted into a table has value exceed the limit of the column's definition, eg, the value to be inserted is something like "abcdef", but the column's definition is something like "varchar(3)". The problem is, there are more than 10 tables that have been inserted new records in that stored proc, and each has huge amount of columns. Is there a way to let SQL Server tell exactly which table and which column it complains about? So I can avoid the extremely tedious task of using the same problematic data to examine each column?
Thanks very much,
|
|
|
|
|
I don't know, so I Googled for "String or binary data would be truncated".
One of the hits[^] looks promising, especially the reply by Axos_Tech.
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
|
|
|
|
|
Thanks, but I cannot see how Axos_Tech's words actually help me in our situation. The post you gave has some words I quite agree: "This is notorious error in SQL." and "didn’t find any elegant way of finding this error." I think, this is one thing MS's dev failed the good practice here
|
|
|
|
|
Since it's an error you should get more info along the message. The whole message should include also the row number for the error so using you should be able to locate the statement. An example of the error:
Msg 8152, Level 16, State 14, Line 11
String or binary data would be truncated. So in that case the error was in the statement at line 11.
On the other hand, by using try-catch[^] block you can catch the error and produce more information about the error (like the statement, parameter values etc) for example using RAISERROR[^] statement
|
|
|
|
|
Ok I have a report list item. this list has textboxes etc inside it and is 7 inches tall. My question is the behavior seems to be that the list repeats by putting the next one underneath it and so on which results in one list showing per page.
What I wish to do is repeat that same list from left to right filling the possible space that way BEFORE dropping down to the next page. With the page in landscape, this would put 3 lists on one page before going to page 2.
So instead of
LIST
ITEM NAME
ITEM NUMBER
page down
LIST
ITEM NAME
ITEM NUMBER
page down
I would like to see
LIST LIST LIST
ITEM NAME ITEM NAME ITEM NAME
ITEM NUMBER ITEM NUMBER ITEM NUMBER
page down
To be clear this list has many more things than those two. that is just for visual reference. Its a very structured list with around 25 items in it including some images . Everything inside the list is out of a database, nothing static.
I have poked, prodded and read until my eyes bleed and either I'm missing something so simple I need to be shot, or this is something not normally done in a report.
This report is embedded in an ASP.net app but I posted here since the issue is with the structure of the report itself, nothing really dealing with asp.net
Programming is a race between programmers trying to build bigger and better idiot proof programs, and the universe trying to build bigger and better idiots, so far... the universe is winning.
|
|
|
|
|
The problem you are having is about presentation, so I would say you are wrong about trying to solve this in the database.
You also say that: gavindon wrote: This report is embedded in an ASP.net app
I get the impression that you're actually not using an Asp.net application for the actual report.
Normally the aspx page would have been the right place to solve the presentation problem, but now I have to ask: What reporting system are you using?
|
|
|
|
|
you are 100% correct sir. I only mentioned the asp.net as a reference to where it all lives. I'm using the VS built in rdlc reports to build this. And it is 100% a presentation problem not a coding one. I came here to database because I am essentially using reporting services functionality for this and thought database people might have more insight into the presentation than a pure coder.
So this is a .rdlc that I show in an asp.net reportviewer , it is then exported form the reportviewer to a pdf and sent off to a printing company. I have all that figured out just this one presentation issue. And it is only an issue due to the fact that we often print just a few here at the office rather than do the whole batch t he printer. If i only had one per page, it is a waste of paper when 3 of them will fit on a page.(assuming I can get this figured out that is.)
Programming is a race between programmers trying to build bigger and better idiot proof programs, and the universe trying to build bigger and better idiots, so far... the universe is winning.
|
|
|
|
|
If your using SQL Server have a look into Pivot Queries[^]
As barmey as a sack of badgers
Dude, if I knew what I was doing in life, I'd be rich, retired, dating a supermodel and laughing at the rest of you from the sidelines.
|
|
|
|
|
SQL server 2008 r2. This might indeed be the answer to my issue. I will have a go at this and see. Thank you for pointing that out. As I am not a database person really never had anything to do with it, Knowing even where to start looking is a huge bonus and thanks again for the pointer. I will update to let you know if that solved the issue.
Programming is a race between programmers trying to build bigger and better idiot proof programs, and the universe trying to build bigger and better idiots, so far... the universe is winning.
|
|
|
|
|
your welcome
As barmey as a sack of badgers
Dude, if I knew what I was doing in life, I'd be rich, retired, dating a supermodel and laughing at the rest of you from the sidelines.
|
|
|
|
|
thats not quite what I wanted to do but it was close. and it was very informative and gave me some more tools for the next project. So problem not solved but still of value and thanks again.
Programming is a race between programmers trying to build bigger and better idiot proof programs, and the universe trying to build bigger and better idiots, so far... the universe is winning.
|
|
|
|
|
I'm playing with the idea to build a sample, on how to integrate the Time Period Library for .NET into the SQL Server.
So far I have build a demo library/assembly/procedure and does some time period calculations using the AdventureWorks catalog ... and it works .
Because I have little experience using CLR SP, here my questions:
- Makes this integration sense to reduce the complexity of T-SQL programming?
- What's the performance difference between CLR SP and T-SQL?
|
|
|
|
|
From what I have read, the CLR SP seems to actually return data quicker than T-SQL but that it becomes much harder to maintain your application. Before going this route you need to determine which is more important, speedy data return, or application maintenance.
|
|
|
|
|
It certainly can, but have you considered user-defined functions?
|
|
|
|
|
Implicitly, yes.
The question is if it makes sense to provide a library which supports time operations for database tables.
Something like:
Overlaps( startDateColumnName, endDateColumnName, testPeriodStartDate, testPeriodEndDate );
usage:
SELECT * FROM Bookings WHERE Overlaps( 'Start', 'End', '20110101', '20110331' )
Another sample may be:
GetGaps( tableOrViewName, startDateColumnName, endDateColumnName, searchStartDate, searchEndDate );
usage:
EXEC GetCaps( 'Bookings', 'Start', 'End', '20110101', '20110331' ) -- return all gaps
|
|
|
|
|
If you use them a lot then sure.
On my last job I made a database that contained only user-defined functions that I then used in many queries from many databases.
User-defined table-valued functions combine the best of views and stored procedures -- they're like views that can take parameters.
|
|
|
|
|
The idea is to provide a generic library with predefined SPs and UDFs that can be used for complex time period calculations (inside, overlap, gap, intersection...).
For example:
EXEC TimePeriodInfo
@table = 'HumanResources.vEmployeeDepartmentHistory',
@startDateField = 'StartDate',
@endDateField = 'EndDate'
returns (only fake sample):
periods:
05.01.1998 - 30.06.2000 | 907.00:00
20.01.1998 - 15.08.1999 | 572.00:00
08.02.1999 - 01.10.2001 | 966.00:00
04.03.1999 - 15.12.2003 | 1747.00:00
28.03.2001 - 30.08.2001 | 155.00:00
31.08.2001 - 15.08.2002 | 349.00:00
intersected periods:
20.01.1998 - 15.08.2002 | 1668.00:00
combined periods:
05.01.1998 - 15.12.2003 | 2170.00:00
|
|
|
|
|
Hi,
CLR procedures are handy and the programmability is much wider than using plain T-SQL. However, depending on the specs it may/may not be wise to use them.
Few thoughts:
- if you're working with sets (possibly with large amount of rows), building a procedure or function that works row-based may cause intolerable response times. SQL is very good in set based operations (it's designed for that ).
- if you need calculations, complex logic etc then the programmability of .Net will offer much more possibilities over SQL and it may be a better option.
Based on the examples you've written, they may be fairly easy to do with SQL using proper conditions etc and in that case the performance would be far better. But as they were only examples I'm not able to say if that's really the situation.
A good rule of thumb could be that there's usually no need to replace such functionality with CLR operations that already exists in SQL itself unless there's some other benefits and not too much downsides.
Best regards,
mika
|
|
|
|
|
Hi Everybody ,
How would i execute 2 select statements in 1 stored procedure using TRANSACT-SQL?
The execution of the 2nd procedure is conditional and based on whether the 1st statement returns any values or not?
Essentially as an example:
select * from cupboard where cupColour = 'white'
if cupboard.CupsFound = 0 then
select * from cupboard
end if
return dataset
Any help would be greatly appreciated...i will continue to search and read
|
|
|
|
|
butchzn wrote: How would i execute 2 select statements in 1 stored procedure using TRANSACT-SQL?
One at a time
Try an
IF EXISTS(<your sql statement here>)
BEGIN
<conditional statement here>
END
I are Troll 
|
|
|
|
|
Hi Thanks for bumping me in the right direction i have managed to solve it with your input!
ALTER PROC [dbo].[FindCupByCupIdAndDescription]
@CupID int,
@CupDescription nvarchar(50)
AS
IF EXISTS(SELECT CupID FROM Cupboard WHERE CupId = @CupId)
SELECT * FROM Cupboard
WHERE CupId = @CupId
ELSE
SELECT * FROM Attic
WHERE CupDescription LIKE '%' + @CupDescription + '%'
this is a really simplified example but is essentially the logical illustration of what i was hoping to achieve using a result as a condition. 
Thanks you.
|
|
|
|
|
You're welcome
|
|
|
|
|
Assuming that you want to use either ID or description and not both (does not make sense) then the following will do the job with a conditional where caluse.
SELECT *
FROM TableName
WHERE (ISNULL(@CupID,0)=0 OR CupID = @CupID)
AND (ISNULL(@CupDesc,'') = '' OR CupDesc = @Cupdesc)
There are a number of variations on this, search for "conditional where clause"
Caveat, string too many of these together (5+) and SQL Server gets very cranky
Never underestimate the power of human stupidity
RAH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
