|
Hi,
I need to check if a Value exists in a Table. I want to create 1 SP for al datachecks for different fields and tables.
I created an SP:
CREATE PROCEDURE DBO.SP_CHECK_BASEVALUE
@CodeInGrid Varchar(255),
@FieldInTable Varchar(255),
@Table Varchar(255)
AS
DECLARE @sql nvarchar(400)
SET @sql = 'SELECT COUNT(*) FROM ' + @Table + ' WHERE '+ @FieldInTable + ' = ' + @CodeInGrid
EXEC sp_executesql @sql
GO
Problem is @FieldInTable. Is says as Error that Column 'Column' does not exist.
How to create an SP that sees @FieldInTable as ColummName as stead of a varchar value.
|
|
|
|
|
You are using dynamic SQL where you are concatenating a string together to make a full statement. You have not sanitised the values @Table, @FieldInTable and @CodeInGrid. This means your application is susceptable to a SQL Injection Attack. Please read this[^]
All column and table names are nvarchar(128)
If you must do this then please check that @Table and @FieldInTable (actually, they are called columns in SQL Server) are valid first
This might be a starting point for the checking that the table and column name are valid:
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @Table
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = @fieldInTable AND TABLE_NAME = @Table
Also, table and column names should be wrapped in square brackets in case they contain obscure characters or whitespace.
@CodeInGrid I presume is an integer as you don't wrap it in apostrophes in your dynamic SQL. If so pass it as an integer and NOT as any sort of string type. This can easily be cast to a string for concatenation with the rest of the satement.
|
|
|
|
|
Hi,
MY SP is now:
CREATE PROCEDURE DBO.SP_CHECK_BASEVALUE
@CodeInGrid Varchar(255),
@FieldInTable Varchar(128),
@Table Varchar(128)
AS
IF EXISTS(
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @Table
)
BEGIN
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = @FieldInTable AND TABLE_NAME = @Table)
BEGIN
DECLARE @sql nvarchar(400)
SET @sql = 'SELECT COUNT(*) FROM ' + @Table + ' WHERE '+ @FieldInTable + ' = ' + @CodeInGrid
EXEC sp_executesql @sql
END
END
GO
still the same error. How to convert?
AND @CodeInGrid is a String Value (CountryISOCode, CurrencyISOCODE, etc)
|
|
|
|
|
spooky manus wrote: still the same error. How to convert?
AND @CodeInGrid is a String Value (CountryISOCode, CurrencyISOCODE, etc)
Then it is being interpreted as a column name because you've not enclosed it in apostrophes. Print out @sql and see if it actually makes sense. You'll see that it doesn't because it is missing the apostrophes around @CodeInGrid
And you still haven't sanitised @CodeInGrid. Does @CodeInGrid really need 255 characters for an ISO code of a few characters?
@FieldInTable and @Table are still the wrong type. They should be NVARCHAR(128).
|
|
|
|
|
spooky manus wrote: How to add the ' to the @CodeInGrid parameter?
You don't add it to the parameter. You add it to the SQL.
There are two ways to do this.
The unsafe, but quick and dirty way: You write two apostrphes next to each other.
The safe, but slower and cleaner way: use sp_executesql[^] stored procedure.
I recommend using sp_exeuctesql as it doesn't require that you do anything funky to apostrophes at all.
|
|
|
|
|
Thanks for your help. Especially the good article about sql injection. Gat about 150 SP's to alter now (seigh). but WILL do that certainly
|
|
|
|
|
I have 2 tables AnalysisHeadings and ProductAnalysisValues.
AnalysisHeadings consist of columns :-
HeadingId
Name
ProductAnalysisValues consist of columns :-
HeadingId
ItemId
Value
What I need to do is select ALL rows from AnalysisHeadings and only the rows from ProductAnalysisValue where the ItemId = 1
The 2 tables are linked by HeadingId to HeadingId.
I stuck on how to limit the the selection of rows from ProductAnalysisValues
I appreciate your help.
Steve Jowett
-------------------------
Sometimes a man who deserves to be looked down upon because he is a fool, is only despised only because he is an 'I.T. Consultant'
|
|
|
|
|
Steven J Jowett wrote: What I need to do is select ALL rows from AnalysisHeadings and only the rows from ProductAnalysisValue where the ItemId = 1
Use an OUTER JOIN to select EVERYTHING from one table, and only matches from the other
SELECT *
FROM AnalysisHeadings AS ah
LEFT OUTER JOIN ProductAnalysisValues AS pav
ON ah.HeadingId = pav.HeadingId
WHERE ItemId = 1
|
|
|
|
|
Actually,
SELECT
*
FROM
AnalysisHeadings AS ah
LEFT JOIN
ProductAnalysisValues AS pav
ON (ah.HeadingId = pav.HeadingId)
WHERE
ItemId = 1
will not get you very far since ItemId will be NULL where there is no matching ProductAnalysisValues row. Therefore, you will get the same result as if you had used an INNER JOIN.
You will need to specify the qualification inside of the ON clause of the LEFT JOIN:
SELECT
*
FROM
AnalysisHeadings AS ah
LEFT JOIN
ProductAnalysisValues AS pav
ON (ah.HeadingId = pav.HeadingId AND
pav.ItemId = 1)
|
|
|
|
|
Well, I'm glad someone spotted my deliberate mistake. Well done! 
|
|
|
|
|
I only spotted it because I committed the same foul on one of my production servers about a month ago. It will be a least a year before I make the same mistake again 
|
|
|
|
|
Michael
Thanks for help with my select query. I now have one that does extactly what's required thanks to you and Colin.
Best regards
Steve Jowett
-------------------------
Sometimes a man who deserves to be looked down upon because he is a fool, is only despised only because he is an 'I.T. Consultant'
|
|
|
|
|
What is the data type for NULL ? I heard that NULL is untyped. But some article's say it's character type. If it is character type, then what about NULL value contained in a numeric column ?
Other article say's NULL's data type is the data type of the column which it belongs to ?
Which is correct ? Any ideas ?
|
|
|
|
|
NULL does not have a type. It can be used as a non-value in a column of any datatype, where that column's definition allows nulls. It is implemented (in SQL Server at least) as an additional bit per nullable column within the row so it doesn't restrict the range of values that can be represented.
In the .NET Framework, NULL's type is DBNull.
Do be aware that different databases implement NULL differently, and not all are completely SQL-92 conformant.
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
Mike Dimmick wrote: It is implemented (in SQL Server at least) as an additional bit per nullable column within the row so it doesn't restrict the range of values that can be represented.
I am not cleared on this. Can you please explain it more ?
|
|
|
|
|
It is just an internal storage requirement. Nothing for anyone writing SQL to actually worry about. How NULLs are represented internally has no effect on your SQL statements.
|
|
|
|
|
Colin Angus Mackay wrote: How NULLs are represented internally has no effect on your SQL statements.
Yes I know. But yesterday we had a discussion on the datatype of null. My friend was arguing it is character type. So I just want to confirm it
|
|
|
|
|
Say that a column is defined as having the smallint datatype. This is a 2-byte integer. The documentation says it can hold values from -32,768 to 32,767. This is the range of a signed 16-bit value. If SQL Server were to hold the NULL as a special value, one value would have to be reserved for that purpose, restricting the range of values you could store.
Instead, each row carries an internal, hidden field called the nullable bitmap. This field is big enough to hold the null value bits for each nullable column in the table, rounded up to a whole number of bytes. If there are 8 or fewer nullable columns, 1 byte will be used, if 9 to 16 nullable columns, 2 bytes, if 17 to 24, 3 bytes, and so on. If the column is set to NULL, SQL Server sets the appropriate bit in the nullable bitmap; if set to an actual value, the corresponding bit is cleared. If this bit is set when reading the record, the value in the field itself is disregarded.
The full details of how SQL Server actually stores data can be found in "Inside SQL Server" by Kalen Delaney (for 2005, "Inside SQL Server 2005: The Storage Engine").
Personally I prefer to avoid NULLs where possible. It can get very confusing differentiating NULLs that are the actual column (non-)value and those that arise from outer joins where no match was found.
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
Thanks Mike. It helped a lot. thanks again
|
|
|
|
|
A null entry denotes the absence of an entry, so you could say that a NULL has no data type because it doesn't make sense to have a type for something that isn't there. I know that this seems a bit esoteric, so bear with me.
How many values can a boolean have? Now, while you may argue that a boolean can only have two values (true or false), you can also have the absence of the value - i.e. null. This doesn't mean that the data type for a null is a boolean here.
So, to answer your question, the NULL item has no type.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
That was perfect. So will oracle also consider in the same way ?
|
|
|
|
|
All standard databases will consider the NULL to be an absence of a value, i.e. without type.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Hi,
I would like to create a query that display the data on a transactional level with the related dimension on each row. I can acheive this using the DRILLTROUGH but it doesn't seem right as this is a top-level query and not a drill-trough.
My query is
DRILLTHROUGH MAXROWS 100 Select ([Measures].[ID])
on 0 From [NBOS]
RETURN [Fact Trade].[ID] AS LiveTradeID,[Fact Trade].[Volume],
[Fact Trade].[Total Volume],[Fact Trade].[Price],
[Fact Trade].[Commission],[Fact Trade].[Commission2],
[Fact Trade].[Trade Count],[$Company].[Company],[$Counterparty].[Company],
[$Instrument].[Instrument Name],
[$Sequence Item].[Sequence Item],[$Date Time].[Full Date Alternate Key]
I looking for something similar to this SQL
SELECT * FROM FactTrade
INNER JOIN Instrument ON Instrument.InstrumentID = FactTrade.InstrumentID
Does anyone know the best practice to do this.
THANKS SO MUCH IN ADVANCE
|
|
|
|
|
Hi all
I'm trying to write a DTS that as it's first step checks a log table as to whether the process has already run for that date, and if it has, exit.
I thought I'd be able to have an 'Execute SQL Task' with something like the following:
DECLARE @ContributionDate DATETIME
DECLARE @NoOfRecords INT
SET @ContributionDate = GetDate()
CREATE TABLE #Results(NoOfRecords INT)
INSERT INTO #Results EXEC db.dbo.vsDissemination_HasRun @ContributionDate, 'C'
SET @NoOfRecords = (SELECT TOP 1 NoOfRecords FROM #Results)
DROP TABLE #Results
IF @NoOfRecords > 0
BEGIN
RAISERROR('Dissemination has occurred',1,1)
END
ELSE
SELECT 1
Which when the error was raised would go down the On Failure branch, but it always goes to the On Success branch.
Running the script in Query Analyzer does either return a numeric value or an error.
Firstly, is this the best way to do this check?
If it is, why doesn't it work - if not, how should I be doing this?
Thanks
Ben
|
|
|
|
|
Try RAISERROR('Dissemination has occurred',16,1).
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
