Question Pass global variable from module to sql as a filter

[nick.h]

Hey guys,

I'm still new at moving from MS access to using Visual Studio 2010 and SQL Server 2008.

In my Access DB I had a public function that I used as a filter for my sql queries. I can not figure out how to duplicate that in Visual Studio. I want to have the user only see records that they added and I created a module that stores a variable and a function (getUser()), but SQL doesn't like it.

It says that getUesr is not a recognized build-in function name. So how do I create the function for it?

Any advice?

Thanks,
Nick

 

[Herman]

Google "stored procedures", that should get you started.

 

[jmcilhinney]

You can't declare a function in VB and then use it in SQL Server. The database knows nothing about your application. You need to call the function in VB and then pass the result to the database as a parameter.

 

[nick.h]

You can't declare a function in VB and then use it in SQL Server. The database knows nothing about your application. You need to call the function in VB and then pass the result to the database as a parameter.

So is stored procedure the right way to go?

 

[jmcilhinney]

You can use a stored procedure or not. That's irrelevant to this topic because passing a parameter is done exactly the same way whether you're using inline SQL or a sproc. You would have had a WHERE clause in your SQL before that presumably contained the function call. Now, you're calling the function in VB and providing the value to the SQL code instead of the function. Everything else is the same though. It's just a SQL query with a WHERE clause.

 

[Herman]

I want to have the user only see records that they added and I created a module that stores a variable and a function (getUser()), but SQL doesn't like it.

Probably the easiest way to achieve this is with a query unless you really have special requirements...

SELECT * FROM Records WHERE Records.Creator LIKE '%John%'

One good reason to use a stored procedure would be if you made the contents of the table encrypted at large, and wanted do decrypt only certain records on demand. You would then create a stored procedure on the server that would accept a username and password and decrypt requested records.