November 7, 2014 at 7:37 am
Hello,
I want to filter data by group but it doesnt work...
I can not find a mistake...could you please help?
Variable @groups is "NameofGroup"
ALTER Procedure amp
(
@group char(20)
)
As
DECLARE @SQLString NVARCHAR(4000)
SET @first = 1
IF OBJECT_ID('tempdb.dbo.##amp') IS NOT NULL
DROP TABLE tempdb.dbo.##amp
SET @SQLString = N'
SELECT *
into ##amp
FROM dbo.query_amp'
IF (@group) is not null
BEGIN
IF @first = 1
BEGIN
SET @SQLString = @SQLString + N' WHERE (w_amp.groups = '+ @group +' ) '
SET @prvy = 0
END
ELSE
SET @SQLString = @SQLString + N' AND (w_amp.groups = '+ @group +' ) '
END
/*********************/
EXEC sp_executesql @SQLString
return
November 7, 2014 at 7:49 am
This doesn't look like it is the entire code. You seem to have a number of conditions in here that can't ever be true. Also, why are you using global temp tables? Not really sure why you are using dynamic sql either. They way you have this coded it is vulnerable to sql injection.
Can you try to explain what you are trying to do?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 7, 2014 at 8:05 am
In addition, the structure of dbo.query_amp would help. There's no reason I can see for using dynamic SQL. If you want to have your passed parameter contain a list of groups, there are other ways to go about it. The CHAR(20) for your parameter also has me curious.
What are you trying to accomplish?
November 7, 2014 at 2:44 pm
Hi,
so here is my explanation.
I use ms access2007 with ms sql database.
In a report form I have two field date1, date2(datetime) and one field for a group(string)
Everything works fine If I use Integer variable, but I need to work with string and I cant get output.
for simple example table view_amp has:
id date1 group
1 1.1.2014 A
2 1.2.2014 A
3 1.5.2014 B
etc...
I do select * from view_amp where group = A just for illustration.
I want to now where is a syntax error in my former posted code, and why I cant get output when I put a string (A) into variable @group.
November 7, 2014 at 2:50 pm
peter478 (11/7/2014)
Hi,so here is my explanation.
I use ms access2007 with ms sql database.
In a report form I have two field date1, date2(datetime) and one field for a group(string)
Everything works fine If I use Integer variable, but I need to work with string and I cant get output.
for simple example table view_amp has:
id date1 group
1 1.1.2014 A
2 1.2.2014 A
3 1.5.2014 B
etc...
I do select * from view_amp where group = A just for illustration.
I want to now where is a syntax error in my former posted code, and why I cant get output when I put a string (A) into variable @group.
We can try to help but it is not at all clear what the problem is or even what you are trying to do. The code you originally posted has a number of issues which all need to be addressed. We can help but you need to post some ddl and sample data along with the desired output. Take a few minutes and read the first article referenced in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 7, 2014 at 3:28 pm
okay I will try to be realy concrete about what I need, you can forget for code before,It would be long way of explanation.
just this:
@group = A
@SQLString = @SQLString + N' WHERE (w_amp.groups = '+ @group +' ) '
I want to know if included variable @group has right syntax...because it is string
Thank you.
November 7, 2014 at 4:07 pm
peter478 (11/7/2014)
okay I will try to be realy concrete about what I need, you can forget for code before,It would be long way of explanation.just this:
@group = A
@SQLString = @SQLString + N' WHERE (w_amp.groups = '+ @group +' ) '
I want to know if included variable @group has right syntax...because it is string
Thank you.
No the way you have this coded won't work. The way you indicate a string in sql is with '. You are using dynamic sql here (which seems like an extra level of complexity that isn't needed). You would need to change this be like w_amp.groups = ''' + @group + ''')'
Still, the dynamic sql seems overkill and using global temp tables is likely going to cause you tons of grief. I have been doing this for close to 20 years and have not once ever needed global temp tables. And from what you describe and the small amount of code you posted I don't see the need for dynamic sql. As I stated before, the way you have this coded your dynamic sql is vulnerable to sql injection attack. I would recommend reconsidering the whole way you are doing this.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 7, 2014 at 4:10 pm
Groups is a character string. So, you've lost your wrappers for it being text.
You need something like @sql = @sql + ' WHERE groups = ''' + @groups ''''.
The '' (double apostrophe, not quotes) is an escape method to include an apostrophe into a string.
If that doesn't work, can you inform us of the exact error message you're getting?
Your comment however about being in Access 2007 shouldn't affect the fact that you shouldn't need dynamic SQL to get where you're going. I recommend you look into what's called passthrough queries. These will allow you to setup procedures in SQL and call them directly from the access project, allowing you to use parameterization, cached query plans, and a number of other benefits.
Also, use #amp, not ##amp. #amp is a local temp table for the call itself (and any subcalls). ##amp is a global temp table that users will share and has a series of special rules that can really shoot you in the foot if two users attempt to manipulate it simultaneously.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 8, 2014 at 2:45 am
Thank you for your hints.
I dont use passthrough queries method(I will look at it), but in spite of I can call sql procedures directly from my access project. Yes, there are more users, they will read report from the same table(query), in the same time, under different conditions. Now I use just one common sql account for all users. Under this one account I have created a table with more users and their access rights.
Can be there a conflict? if users will work with report in the same time, under one sql account , and they call ##amp or #amp ....
What would you recommand?but i want to use only one sql account.
November 8, 2014 at 4:38 am
Quick though, since you are already using sp_executesql, you should consider passing the parameter using the parameter string parameter of that procedure.
😎
November 8, 2014 at 2:51 pm
can you give me an example?
November 8, 2014 at 9:49 pm
peter478 (11/8/2014)
can you give me an example?
Quick example, more info here sp_executesql (Transact-SQL)
😎
DECLARE @COL_NAME NVARCHAR(128) = N'service_id';
DECLARE @PARAM_STR NVARCHAR(255) = N'@COL_NAME NVARCHAR(128)';
DECLARE @SQL_STR NVARCHAR(MAX) = N'
SELECT
C.object_id
,C.name
,C.column_id
FROM sys.columns C
WHERE C.name = @COL_NAME;'
EXEC SP_EXECUTESQL @SQL_STR, @PARAM_STR, @COL_NAME;
November 10, 2014 at 2:56 am
Evil Kraig F (11/7/2014)
Groups is a character string. So, you've lost your wrappers for it being text.You need something like @sql = @sql + ' WHERE groups = ''' + @groups ''''.
The '' (double apostrophe, not quotes) is an escape method to include an apostrophe into a string.
If that doesn't work, can you inform us of the exact error message you're getting?
Your comment however about being in Access 2007 shouldn't affect the fact that you shouldn't need dynamic SQL to get where you're going. I recommend you look into what's called passthrough queries. These will allow you to setup procedures in SQL and call them directly from the access project, allowing you to use parameterization, cached query plans, and a number of other benefits.
Also, use #amp, not ##amp. #amp is a local temp table for the call itself (and any subcalls). ##amp is a global temp table that users will share and has a series of special rules that can really shoot you in the foot if two users attempt to manipulate it simultaneously.
1. Yes, this works fine ''' + @groups ''' for character string
2. concerning global temp table ##amp you are right , but when I change it to #amp I can get output, no feedback (select * from #amp)
with global it works.
Remark, I use just one sql account.
November 10, 2014 at 6:50 am
peter478 (11/10/2014)
2. concerning global temp table ##amp you are right , but when I change it to #amp I can get output, no feedback (select * from #amp)
with global it works.
That is because your temp table is out of scope. When you declare your temp table in dynamic sql, statements outside the dynamic sql can't see the temp table. Drop the dynamic sql and this issue will fix itself.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 10, 2014 at 11:50 am
The single or multiple SQL User doesn't matter, just FYI. Most webapps run that way with hundreds of thousands of unique users on the far end.
What's happening with #amp is you need to make sure everything happens in the same batch, or statement. Because you're using dynamic SQL and instantiating multiple unique calls, you're losing your SPID. They need to be made in one huge call if you're going to send it in that way.
I don't recommend sending it in that way.
Build procedures on the SQL server, use passthrough queries to call them, or heck just skip the ADP and do a direct connection to the SQL server similar to web apps. Passthrough queries are really just to make it newbie friendly.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply